September 7, 2010 at 9:09 am
Hi All
I want to extract data from multiple Excel sheets and then create different tables for that data in the SQL server and load that data into the respective table. I want to do this using SSIS. I know we can do this by creating separate package for each file but is there any way we can do this operation in a single package?
Looking for this type of solution
File1... file n ---extract data--> SSIS ---createtablewithvar-->SQLSERVER tab1 .. tab n
Thanks in advance....
September 7, 2010 at 9:17 am
sql.abhishek (9/7/2010)
Hi AllI want to extract data from multiple Excel sheets and then create different tables for that data in the SQL server and load that data into the respective table. I want to do this using SSIS. I know we can do this by creating separate package for each file but is there any way we can do this operation in a single package?
Looking for this type of solution
File1... file n ---extract data--> SSIS ---createtablewithvar-->SQLSERVER tab1 .. tab n
Thanks in advance....
You'll need to generate the SQL Server tables in advance. If they don't exist, you can't use them as destinations in your data flows.
Iterating around a collection of files is not difficult - you'll probably use a Foreach loop to do that. But if every file has a different structure, you'll need a separate data flow for each.
A single package can definitely do this, though I suspect not in the dynamic way you desire.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 7, 2010 at 9:22 am
Thx Phil ...
I know but i am just curious about makin it dynamic .... but i am not able to think of a way to do so ...
September 7, 2010 at 9:28 am
I can think of one way:
1) Write some code that analyses the Excel sheets and does the DDL stuff for you.
2) Write more code which builds your package, using the newly created tables from (1).
3) Test - after a few days, you might be getting close 🙂
All in all, a complete nightmare. Don't do it unless you have to.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 8, 2010 at 1:21 am
Phil is right. Don't bother to make it dynamic if there are only a handful of Excel files. However, if there are a lot of Excel files (say more than 20 or 50), each with a different structure, then you can try the dynamic approach.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply