July 6, 2011 at 5:23 am
HI All
I haeve three sheets in Excel spreadsheet which have same table schema and I am trying to export all of these sheets data into Sql table using SSIS package.
Can any one tell me how to do that using SSIS package?
Regards
July 6, 2011 at 2:48 pm
Is this a one time deal?
If so don't bother with a ForEach loop, I'm not sure of a good way to do it that way anyway..
But with only three it might be easier to just build a dataflow once and then copy/paste it twice and then fix them.
CEWII
July 6, 2011 at 10:05 pm
Gyeah... yeah, I know how, you won't like it though.
Use a script task to build a component list for the ForEach loop using VBA for Excel, then for each of those you'll have to use an expression to control the connection to Excel to attach to the right sheet. It's really the only way with variable sheet names. If the names are always the same you can just build the component list directly.
I don't have an example but I'm sure it's on the web somewhere if it's variable names on the sheets.
The easier way to do this is to treat the sheetnames as tablenames and use an OLEDB source (I think, I'd have to re-research it) and just run T-SQL commands against the sheets.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 6, 2011 at 11:59 pm
1) You have use the "for each ADO.NET schema rowset enumerator" as enumerator in the "for each loop container".
2) then mapped the string variable to "2" in the "variable mapping" tab of "for each loop container".
3) then in the excel source choose "table name or view name variable" and select the variable previously mapped as "2".
I think this will be useful for you.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply