Link to home
Start Free TrialLog in
Avatar of Coloplast
ColoplastFlag for Denmark

asked on

SSIS - Reading multiple sheets from Excel files into SQL DB

In SSIS:
I have several excel files containing multiple sheets.
I need to read the 12 sheets from each file named by each month (Jan - Dec).
The files also contains other sheets by I'm only interested in the months.
I have created a SQL DB containing a table for each month.
I have also created another table which contains the name of each months where a script task reads all the records from.
After the script task I have a for each loop which contains the data flow. I have attached some screens shots.
I cannot get the excel source to read from the object variable containing the name of the month.
I need help to configure this.
Thanks.
Control-Flow.JPG
Data-Flow.JPG
Connection-Managers.JPG
Avatar of Coloplast
Coloplast
Flag of Denmark image

ASKER

Here are the images:
User generated imageUser generated imageUser generated image
ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Source has to be excel, they are typing data into a template and they are trained typing the data correctly :-)
Importing into 12 different even though the schema is the same is because then our BI department wouldn't need to change their logic.
I don't think I understand your last comment with the SELECT query.

If you have suggestion for a better solution, maybe you could create in SSIS and drop me some screen shots?

Thanks
Hi Jim,
Could you please give an example of your comment: "A vastly better idea would be to write a SELECT query that contains months, then use that in the container to loop."
Thanks