Looping through Excel worksheets using SSIS
Here is what I am trying to do. I have a single excel spreadsheet that I want to import. The data structure remains the same but the worksheet names changes every so often. I simply need to read the single spreadsheet and no matter the worksheet name import the data.
I have been attempting to do this in SSIS using a foreach container.
The format of the foreach container is this:
1.Foreach ADO.NET Schema Rowset Enumerator. The connection is set up to the Excel data source and the schema is set to table.
2. The variable mapping is set to use the Sheet Name package variable (this is the same variable I am trying to apply to my Data Flow task) and the index is set to 0.
I am just missing something in the logic of this process. I have been able to loop successfully through multiple Excel workbooks using the foreach file enumerator, but when I try to drill down and do the actual worksheets using the above settings it fails.
I have been attempting to do this in SSIS using a foreach container.
The format of the foreach container is this:
1.Foreach ADO.NET Schema Rowset Enumerator. The connection is set up to the Excel data source and the schema is set to table.
2. The variable mapping is set to use the Sheet Name package variable (this is the same variable I am trying to apply to my Data Flow task) and the index is set to 0.
I am just missing something in the logic of this process. I have been able to loop successfully through multiple Excel workbooks using the foreach file enumerator, but when I try to drill down and do the actual worksheets using the above settings it fails.
Last Comment
ASKER
If I drag the data flow task into the foreach loop container there is no error message generated. The project runs but no data is extracted from the worksheet and inserted into the database. If I drag the data flow task outside the following error message is generated.
And yes I can import the worksheet manually if I specify the worksheet name.
SSIS package "Copy of Foreach.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Error: 0xC0202042 at Data Flow Task, Excel Source [1]: A destination table name has not been provided.
Error: 0xC004706B at Data Flow Task, DTS.Pipeline: "component "Excel Source" (1)" failed validation and returned validation status "VS_ISBROKEN".
Error: 0xC004700C at Data Flow Task, DTS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Data Flow Task: There were errors during task validation.
Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREA
SSIS package "Copy of Foreach.dtsx" finished: Failure.
And yes I can import the worksheet manually if I specify the worksheet name.
SSIS package "Copy of Foreach.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Error: 0xC0202042 at Data Flow Task, Excel Source [1]: A destination table name has not been provided.
Error: 0xC004706B at Data Flow Task, DTS.Pipeline: "component "Excel Source" (1)" failed validation and returned validation status "VS_ISBROKEN".
Error: 0xC004700C at Data Flow Task, DTS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Data Flow Task: There were errors during task validation.
Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREA
SSIS package "Copy of Foreach.dtsx" finished: Failure.
ASKER
I am not sure if I have even done this procees right from the very beggining. It might be easier to just describe the correct way to do this process rather than trying to troubleshoot my flawed, newbie code. :)
Well, after you drag the data flow into the iterator you have to double click on the data pump (it will open a new window) and set the data pump up.
You need to tie the output of the iterator to the source of the excel sheet in the data flow, and you need to tell it what the destination table is.
I'm pretty certain there is a step by step example in the SSIS help.
and here it is on the web:
http://msdn.microsoft.com/en-us/library/ms345182.aspx
You need to tie the output of the iterator to the source of the excel sheet in the data flow, and you need to tell it what the destination table is.
I'm pretty certain there is a step by step example in the SSIS help.
and here it is on the web:
http://msdn.microsoft.com/en-us/library/ms345182.aspx
ASKER
I have attached screenshots of what I have set up so far. I have tried using the link from Microsoft, but it assumes you know quite a bit about SSIS and leaves a lot of steps out.
Hopefully the screenshots will give you a better idea of what I am doing.
When I run the package as I have it set up in the screenshots it does not fail. The foreach conatiner is the only object that turns green. The data flow task doesnt seem to do anything.
Appreciate your help so far.
Oh and by the way, what is the data pump? The foreach loop box?
foreach-setup-screenshots.doc
Hopefully the screenshots will give you a better idea of what I am doing.
When I run the package as I have it set up in the screenshots it does not fail. The foreach conatiner is the only object that turns green. The data flow task doesnt seem to do anything.
Appreciate your help so far.
Oh and by the way, what is the data pump? The foreach loop box?
foreach-setup-screenshots.doc
Does this EE ost help:
http://www.experts-exchang
I am at a disadvantage as I don't have SSIS on thie computer.
http://www.experts-exchang
I am at a disadvantage as I don't have SSIS on thie computer.
ASKER
Ok under my varible mappings on the foreach loop container box i changed the Index to 2. I had set it to 0 earlier and was getting all kind of errors. I then set it back to 2 (which is where it was set all along) and suddenly it magically started working. I have no idea why changing it from 2-0-2 made it work.
This is another gripe I have is that no where in any documentation I have read does it explain what the index is and how I should know what to set it to. Sorry I am just venting. I do appreciate all the help.
This is another gripe I have is that no where in any documentation I have read does it explain what the index is and how I should know what to set it to. Sorry I am just venting. I do appreciate all the help.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
There's also this one:
http://www.sqlis.com/55.as
Glad you got it working. Sounds frustrating.
You can use debug mode to inspect what's in your variable, but again I can't help too much because I don't have SSIS installed.
http://www.sqlis.com/55.as
Glad you got it working. Sounds frustrating.
You can use debug mode to inspect what's in your variable, but again I can't help too much because I don't have SSIS installed.
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
Can you import one of the worksheets manually in SSIS? (without using the iterator)