SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 SSIS - Delete files
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

littlewing
Starting Member

33 Posts

Posted - 08/28/2008 :  13:15:50  Show Profile  Reply with Quote
Hello, any suggestions on the best way to do this? I have a SQL task that executes and the resultset is a list of filenames. I need to delete these files from the file system. I'd like to make the paths to the files configurable as I move the SSIS package to different servers (dev, staged, prod). Is a script task the best way to go about this?
How do I access and loop through the record set in a script task or a For Each task?

Thanks.

Edited by - tkizer on 08/28/2008 13:44:27

tkizer
Almighty SQL Goddess

USA
36907 Posts

Posted - 08/28/2008 :  13:44:15  Show Profile  Visit tkizer's Homepage  Reply with Quote
Moving to the SSIS forum...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

georgev
Posting Yak Master

United Kingdom
122 Posts

Posted - 08/29/2008 :  10:51:28  Show Profile  Reply with Quote
Set up 2 package variables (SSIS > Variables
objFiles with the data type of Object
strFile_Name with the data type of String

Create an Execute SQL Task, and set the ResultSet property to Full Result Set.
Set up your query that returns the file name, taking note of which column contains the file path (first column = index 0).
Change from the General "tab" to the Result Set "tab" and click Add.
For the Result Name column, replace NewResultName with 0 and set the Variable Name column to User::objFiles
Drag a Foreach Loop container on to your package and attach the green output line from the SQL task to the container.
On the Collection "tab" of the Foreach container, set the Enumerator to Foreach ADO Enumerator and the ADO object source variable to User::objFiles
On the Variable Mappings "tab" add the variable User::strFile_Name and set the Index to the column index we noted above.
Drop a File System Task in to the container, set the Operation type to Delete File, the IsSourcePathVariable to True and the SourceVariable to User::strFile_Name.

Run the package!
Hope this makes sense (and works *ahem*) :)


George
Go to Top of Page

JayantPatel
Starting Member

USA
1 Posts

Posted - 12/18/2013 :  10:09:42  Show Profile  Reply with Quote
George,

I have created package as you have suggested, but I am still not able to delete files from list of file in the table. What am I missing here?

I have two columns in the table, 1. FileName with file extention example E345545.csv and 2. filepath example C:\\...\...\.

Thanks in advance

JP

JP
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 12/19/2013 :  04:11:12  Show Profile  Reply with Quote
quote:
Originally posted by JayantPatel

George,

I have created package as you have suggested, but I am still not able to delete files from list of file in the table. What am I missing here?

I have two columns in the table, 1. FileName with file extention example E345545.csv and 2. filepath example C:\\...\...\.

Thanks in advance

JP

JP


Then in your case only difference would be to add a variable to hold fullpath of file and use EvaluateAsExpression true for it. Then set expression as
@[User::PathVariable] + "\\" + @[User:fileNameVariable]

the two variable will be used inside ForEachLoop and mapped to index 0 and index 1 to get values from the object variable.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000