I have been researching this for a while and not found anything explicit. I have multiple Excel files containing billing information for different projects. We can think of these as File A for project A, File B for project B and File C for project C. They are moved from outlook, by rule, to a common folder.
I would like to use SSIS to utilize the file name in determining the destination location. In our very simplified case above, I would like loop through the folder and have File A to be read to Table A, File B to Table B and File C to Table C. All tables are contained within one SQL Server Database. Once complete I would like the files to be moved into set destinations (Folder A, Folder B, and Folder C)
I assume you will have to add a script component to take the file name variable, parse it to recover the A, B, C and use this to direct some sort of IF statement to locate the relevant destination location.
The only other minor complication is that I would want to check each table to ensure I am not adding any duplicate data - assuming I would use a look up component here before each table destination...
I can find information on for each loop containers and dynamically setting source locations, but not dynamic destinations based on file names.
Does this seem like a reasonable undertaking in SSIS and would anyone able to help me on this or perhaps point me to a source that I have yet uncovered?
Any help would be really appreciated - please also let me know if you would like further details.
Thanks
As read in the comment, We cannot load into multiple tables with in a for-each loop as long as they share different table structure.
If All excel sheets share same structure, we can avoid the complexities. the following steps may help you.
1. DFT 1
a. Develop an SSIS package with a DFT to load all your excel files into a single stage table.
b. In the table in addition to the excel columns, add a separate column to store file name.
c. Use a for-each loop with 'Foreach ADO.NET Schema Rowset Enumerator'.
d. Again it will be help you if excel files share a common file name pattern.(ProjA_140527.xls, ProjA_140528.xls,ProjB_140527.xls etc),
so that you can use Proj*.xls to fetch the excel files.
2. DFT 2
a. After this DFT, add another DFT. use an OLE DB Source taking data from stage table.
b. After the source, add a conditional split. Write expressions based on excel file name column.
c. if you have 5 projects, conditional split will have 5 outputs and 1 non-matching output.
d. Map each project tables to conditional split outputs.