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)
 Move Files
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Brittney10
Posting Yak Master

USA
152 Posts

Posted - 05/15/2013 :  15:38:53  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 05/16/2013 :  00:38:36  Show Profile  Reply with Quote
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
  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.33 seconds. Powered By: Snitz Forums 2000