Working with SSIS Part#1

It is often necessary when working with customer data to do import routines that will input each individual customer’s data. One way whihc is often implemented in Sales is to have the individual sales personnel input their weekly prospects into a predefined Excel spreadsheet and then to send that off to be placed on a common directory location. Your job would eb to write the SSIS package that would process it into the database. Luckily with SQL Server Integration Services 2005 this is an easy task.

1. Drag a ForEach Container onto the work surface. For this we will want to also set a variable. So create a new string variable in the variable pane called strCurrentFile. This is what we will store the current file being accessed in the Loop.
2. Rigth click on the ForEach Container and select Edit. Click on Collections on the left hand pane and select ‘Foreach File Enumerator’ from the Enumerator drop down at the top. Now browse and select the folder that you wish to be searched and place ‘*.xls’ in the Files box in order to tell the control that you want all of the Excel files in this directory. We also want to make sure that the fully qualified path option is selected. Once you are done your Properties pane should look something similar to the image below.
4. Now click on the Variable Mappings section and map your variable you created earlier, strCurrentFile, to the 0 index which corresponds to the file path of the current object. This will now populate our variable and allow us to work on this one file independently within the context of the container.
5. Now drag a Data Flow task object into the Containe. Right click and select Edit.
6. Now drag an Excel data source onto the canvas. Configure it to point to a default instance of the Excel file in your directory. This is onyl so that it creates the needed Excel Connection Manager. Once the Manager is created…right click on it and select ‘Properties’.
7. Now select the button next to Expressions. This is where we will edit the Manager in order to make it dynamic. Set the ExcelFilePath property to read from your strCurrentFile variable. After you are done your properties window should look similar to this..
9. Now you can do whatever you want to with this particular file within the loop. Normally it is a good idea to then archive the Excel file after processing so that if any problems occur then your folder will not be muddled with files that have already been processed. So you could set up the container to resemble the following routine.

Hopefully, this has helped you understand how to dynamically process a directory of files. The same routine can be applied to any file type you just need to adapt the steps for the particular Connection Manager object that you are utilizing.