Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SSIS and Import/Export (2005)
 SSIS - Delete files

Author  Topic 

littlewing
Starting Member

33 Posts

Posted - 2008-08-28 : 13:15:50
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.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-28 : 13:44:15
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

122 Posts

Posted - 2008-08-29 : 10:51:28
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

1 Post

Posted - 2013-12-18 : 10:09:42
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

52326 Posts

Posted - 2013-12-19 : 04:11:12
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
   

- Advertisement -