November 10, 2011 at 1:32 pm
I am setting up a SSIS package where I have to pull data from multiple Excel files and transform it into one table. The names of Excel files are dynamic. For example 1011AB.xls (where 10 = October, 11 = Year, AB = user's initials). The initials are stored in the user's table, the month and year are going to be sent to the package from a .NET application, which will execute the package.
My biggest problem is figuring out on how to loop through the table to pull user's initials, build the Excel's Source File's connection string dynamically for every file based on those initials, month and year; and finally do a Data Transformation from the Excel into another table in the database. All Excel files formatted exactly the same, except they all contain different values.
Any suggestion is appreciated.
Thank you very much!!!
November 10, 2011 at 1:51 pm
First you need to create the connection here is a step by step approach: http://www.bidn.com/blogs/kylewalker/ssis/997/setting-up-an-ssis-package-with-a-dymanic-excel-source
Then you need to loop through the files and here is also a step by step guide: http://dwhanalytics.wordpress.com/2011/03/16/using-ssis-loading-multiple-excel-files/[/url]
I hope this helps
Iulian
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply