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.
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 |
|
georgev
Posting Yak Master
122 Posts |
Posted - 2008-08-29 : 10:51:28
|
Set up 2 package variables (SSIS > VariablesobjFiles with the data type of ObjectstrFile_Name with the data type of StringCreate 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::objFilesDrag 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::objFilesOn 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 |
|
|
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 advanceJPJP |
|
|
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 advanceJPJP
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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|