SSIS - How to loop through files in folder and get path+file names and finally execute stored Procedure with parameter as Path + Filename

Question

Any help is much appreciated. I am trying to create an SSIS package to loop through files in the folder and get the Path + filename and finally execute the stored proc with parameter as path+filename. I am not sure how to get the path+filename and insert the into the Stored proc as parameter. I have attached the screenshot for your reference:

Teheran

Answer 1

Looks like you have the right idea in general and the link @Speedbirt186 provided has some good details but it sounds like there are a couple of nuances that I thought I might point out in regards to flow and variables.

The foreach loop can assign the entire path or the file name or file name & extension to a variable. The latter will be the most help in your case if you don't want to add a script task to split the Filename from the path. If you start by adding 5 variables to your project it will make it a little easier. 1 will be the Source Directory Path, another the Destination (Archive) Directory Path, and then 1 to hold the File Name and Extension assigned by the for each loop. Then 2 additional dynamic variables that simply combine the source directory and file name to get the source full path and the destination with file name to get the destination full path.

Next make sure you set up your database and Excel file connections. In your Excel file connection after setting it up go to Expressions in the properties window and set the "Connection String" property to SourceFullPath. This will tell the connection to change the file path at every iteration of your loop.

Now you just need to setup your loop etc. Add the fore each loop container setting a directory, filter, and choose File Name and Extension.

Now in the expression box on the collection page set the directory property to be that of your Source Directory variable.

The last part of the Fore each loop is to set your variable mappings to store the file name in your variable. so go to that tab choose your file name variable and set index to 0.

At this point you can add your data flow and setup your import just like you would with a normal file (note your default value for your file name parameter should be to an actual file with the structure you will want to import).

After your data flow drop in your Execute SQL task and set it up how you need. here is an example of direct input and you can see an easy way to reference a parameter is simply a question mark (?).

Next in your sql task setup your parameter mapping by adding in the details you need such as:

Now you are on to your file task. Drop your file task and setup as you desire, but choose your destination and source full path variables to tell the task which file to move.

that's it your are done. there is 1 more thing to note though. The way you have your precedence set in the image you posted you show going from your data flow to your sql and to your file task simultaneously. If your stored procedure relies on your file you may want to put it after your sql task. You can always change the constraint options to "completion" if you want to move the file even if your stored proc fails.

Cressler

Thanks for the detailed answer! – Teheran

Thanks @Cressler for detailed answer. One correction, In "Destination variable" for "File system Task" editor, we have to provide Destination directory instead of Destination Full Path. – Aquatint

@Aquatint It is possible that Microsoft allows for a helper method of specifying only folder at which point it will use the same name as the source file however this code is correct and was tested. The use of full path allows you to be able to change a name at the time of the move. So if you are not changing the name of the file perhaps your suggested edit would work. – Cressler

@Cressler - Yes, you are correct. Earlier, I'm using two "File system tasks" (one for moving file and another one for renaming). Now, I'm using only one "File system task" for renaming which will move file as well. Thank you! – Aquatint

Answer 2

What you want to do is to create a variable in your package, call it something like Filename. In the Edit window of the Foreach you can configure that variable to be set (on the Variable Mappings page- set index to 0).

To create a variable, you will need to have the Variables window showing. Use the View menu to show it if it's not currently open.

Then when calling your stored procedure you can pass the then current value of the variable as a parameter.

This link might help: https://www.simple-talk.com/sql/ssis/ssis-basics-introducing-the-foreach-loop-container/

Sill