Avatar of Coloplast
Coloplast
Flag 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
Microsoft SQL ServerSSIS

Avatar of undefined
Last Comment
Coloplast

8/22/2022 - Mon
Coloplast

ASKER
Here are the images:
Control flowData-Flow.JPGConnection-Managers.JPG
ASKER CERTIFIED SOLUTION
Jim Horn

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Coloplast

ASKER
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
Coloplast

ASKER
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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck