SSIS - move only the files names returned in SQL command

Question

I have a SQL statement that returns 600 PDF filenames that I need to use to move a copy of the file out of folder into a new folder (The filenames in the SQL are the exact name of the PDF files in the folder).

I have a execute SQL statement set up that passes the PDF name result set to a variable.

How can I use this variable to pass to file system task to only copy out the PDF filenames returned in SQL statement and passed to variable?

Thanks,

Also I am getting an eror

Answer 1

This is a pretty simple design pattern in SSIS. Generally speaking, you'll get your list of file names from an Execute SQL Task, set up a Foreach Loop Container to process each file, and a File System Task inside the loop.

To start, you'll want to make sure you have the following variables:

I'm using E:\Import\ as my source folder. That's where I expect all my files to be. You can change this to be the folder where your files are. I want to move files into a subfolder, so I've set the DestinationFolder to E:\Import\Internal\. Again, change this to suit your needs.

Additionally, you can see we have a Filenames variable of type System.Object. This is the ADO object that will hold the results of our SQL query. The Filename string variable will be used to store each file name as we go through the loop.

SourcePath and DestinationPath we're going to configure to be populated by an expression. We're going to concatenate the folder names with our file names. To do that, open the variables window, and click on SourcePath. Then edit the Properties and set EvaluateAsExpression to True, and then set the expression to be @[User::SourceFolder] + @[User::Filename]. Do the same for DestinationPath, using the @[User::DestinationFolder] variable in the expression. You should end up with your two *Path variables looking something like this:

Now we can configure the Execute SQL Task. Make sure you set the ResultSet value to Full result set and map the results with Result Name equal to 0 to your ADO object variable Filenames.

Next, create a Foreach Loop Container and configure it to use the Foreach ADO Enumerator type and point it to your Filenames variable.

Go to Variable Mappings and make sure to map User::Filename to Index 0.

Finally, create a File System Task and put it inside the loop container. Then configure it to use your *Path variables with the appropriate action.

When you're all done, you should having a package that looks like this: