SSIS - Reading multiple sheets from Excel files into SQL DB

Coloplast
Coloplast used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ColoplastIT engineer

Author

Commented:
Here are the images:
Control flowData-Flow.JPGConnection-Managers.JPG
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
Any way you can avoid this altogether?  There are multiple reasons I would not choose to go down this road..

>I have several excel files containing multiple sheets.
>I need to read the 12 sheets from each file named by each month (Jan - Dec).
Excel is great for users entering data however they wish.  Similary, Excel is terrible as a data source because users can enter data however they wish, causing a normalized data load to fail.

>I have created a SQL DB containing a table for each month.
Why?  Assuming the schema of the Jan-Dec tabs are the same, why would you import into 12 separate tables, as opposed to a single table with an added column for month?

 >I cannot get the excel source to read from the object variable containing the name of the month.
A vastly better idea would be to write a SELECT query that contains months, then use that in the container to loop.
ColoplastIT engineer

Author

Commented:
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
ColoplastIT engineer

Author

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial