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)
 Move Files

Author  Topic 

Brittney10
Posting Yak Master

154 Posts

Posted - 2013-05-15 : 15:38:53
I have filenames and filepaths that are stored in a table in a SQL db table. I'm using a flat file provided by a client to determine which files (by filename) I need to process and move. I need to move those specific files to another location. Any thoughts on how to do this dynamically in SSIS? What's the easiest way to do this? Basically I need to loop through each record, store the full filepath in a variable, and then move the file. I just can't get it to work.

Thanks in advance for the help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-16 : 00:38:36
its easier

The package will look like this

1. Create a variable called FilePaths of type Object in SSIS
2. Add a Execute SQL task with query to retrieve the filepath values from table. Set Resultset option as Full ResultSet and in Resultset tab map it to object variable created above.
3. Add a foreachloop with ADO.NET Enumerator and map it to Object variable
4. Add a string variable to hold filepath value during each iteration within loop and use it inside Forloop to map and get filepath value
5. Add a FileSystemTask inside for loop with option set as Move file. Set IsSourcePathVariable to true and map the Sourceconnection to the above created variable. Add the destination path connection either directly if static or through a variable if you want to make it dynamic.

Then once you execute package provided you creatd variable and did mapping properly it will iterate through files as per table values and move them to your destination location.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -