Importing Data from Multiple workbooks to SQL using dtsx package

Question

Hello All,

I am a beginner in SSIS and dtsx..
I have a folder where there are multiple workbooks. And each workbook is having multiple sheets without any specific naming convention. But the data in each of the sheets are in a specific format. for eg. 1st Col:Name, 2nd Col:Address, 3rd Col:Contact No., etc.

I have to create a package which will browse thru all workbooks (and all sheets inside them) in the folder and push the data to a table(which is already present) in SQL database.

Someone Please HELP me... Atleast if I can go thru any sites which explains all these

Thanks,
Partha

Answer 1

are sheets having specific names or they also randomly named?

---

Thanks Dileep

Answer 2

Hi,

    The follwing link will help you 
 
    http://www.sqlis.com/post/Looping-over-files-with-the-Foreach-Loop.aspx

---

Lakshman

Answer 3

Hi,

you can
1. use foreach loop and loop through the workbook names you have in that folder and have the names of the workbook in the looping variable
2. Use expressions for variable or Script task to make connection string for the current file and store in same variable sat ConnVar
3. assign this ConnVar variable to your Excel source as connection

now with each loop it will take one my one workbook and use it in you Data flow.

---

Rahul Kumar, MCTS, India, http://sqlserversolutions.blogspot.com/

Answer 4

Hi,

  Are you using same sheet in every work book then it is fine, you can select the sheet in the destination it self. other wise it is some what difficult to do.

---

Lakshman

Answer 5

If all the work books are having same sheet names say like "sheet1", "sheet2" and "Sheet3" and have same number of sheets, you can go a head with the above process. Else you need to use a script component to fetch the each sheet name and use the same variable in excel source connection manager by setting data access mode to
"Table Name or View Name form variable".

---

Thanks Dileep

Answer 6

1.Create a ADO.NET Connection
   Select .NET Providers for OLEDB\Microsoft Jet 4.0 OLEDB Provider. Select the excel workbook using Browse
   Go to All and set Extended Properties to Excel 8.0

2.Create a variable as SheetName with value as a valid sheet (Inof$)

3. Take a Foreach loop and inside it take a DFT
    Edit the For Each Loop:
   Collection: Foreach ADO.NET Shema Rowset Enumerator
   Enumerator Configuration: Select the ADO.NET Connection created above.
   Select Table as Schema.
   Variable Mapping: Select a variable (SheetName) that will capture the sheet name and set the index as 2.

Take the excel source in DFT and define a excel connection manager (MYExcel) for it. Select the data access mode as TableName/ViewName variable and select the SheetName variable from the drop down box.
Now complete the data flow as per your requirement.

Note:
This will work for one excel work book.
For multiple workbooks you need one more foreach loop and add the earlier defined foreach loop inside this.
Select the collection as for each file enumeraror. Selec the folder where the workbooks are located.
Retrieve file name: fully qualified.
Capture the workbook name in a variable (excelfilepath) using Varibale Mapping.
Give a valid filepath to this variable while creating. (C:\A.xls)
Then go to MYExcel connection manager's Property. Go to Expressions and select the Conenction String property. Under Expressions, click on ellipsis (...) and
write
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + @[User::excelfilepath] + "
;Extended Properties="  +"\"EXCEL 8.0;HDR=YES;;"  + "\";"

---

Nitesh Rai- Please mark the post as answered if it answers your question

Answer 7

Hi Dileep,

Thanks for your quick response.

The sheets are randomly named...

Please let me know if u need any other info....


regards
Partha

Answer 8

Hi Dileep,

Please guide me how to import from multiple wookbooks (different names) having different sheetnames inside them.

For example:

Foldername --->                                                C:\DATADUMP

1st Wookbook name under DATADUMP folder --->           May09A

Sheet names inside 'May09A' Woorkbook --->                   Part1
                                                                                     Part2

2nd Wookbook names under DATADUMP folder --->         May09B
                                                                                      SecA
                                                                                      SecB
                                                                                      SecC

And so on...



Just wanted to tell that there is no particular standard naming for either workbook or the sheets inside them.


Hope I have explained my doubts clearly. Please help...



Thanks a ton for your help in advance :-)

Regards
Partha

Answer 9

You need to add 2 for each loops and one ADO.NET Connection Manager.
Please refere to my earlier post

---

Nitesh Rai- Please mark the post as answered if it answers your question

Answer 10

I tried but its not working.. its saying that the connection is already open...

do u have any link to do the task step by step ?

actually I am new to this... thats why facing this kind of issues...

Pls help.

Regards
Partha

Answer 11

I dont have any link but i tried to provide a step by step procedure.

When are you getting "connection already open" message?

---

Nitesh Rai- Please mark the post as answered if it answers your question

Answer 12

what is the version of excel you are using is it 12.0(office 2007) or 8.0(office 2003)?

---

Thanks Dileep

Answer 13

Hi All,

This is just to inform that my package is working !!!

It runs properly if all the below conditions satisfies:

1. Workbook names inside the folder are different but the sheets inside these are of the same name.
2. It takes only the first sheet for each workbook.
3. Works with only excel 2003 version.


It will be really helpful if someone can give me a better solution because:

1. Practically speaking, the workbooks will be of different names (this issue is resolved) and the sheets inside each of the workbooks will be of different names(still this issue is not resolved).
2. Each workbook will have multiple sheets and my package should traverse thru all the sheets and upload the data (this issue is not yet resolved).
3. It should be able to handle excel 2003 as well as 2007 formats.

I am really thankful to each one of you for the solutions which you all gave :-)

Hope I have defined my requirements clearly...

Will be waiting for help.

Regards
Partha

Answer 14

Hi Partha,

1. First use a foreach loop  container to iterate throuh each of excel file.
2. Use a script component, In that maintain a variable to capture the all the sheet names in an array.
3. Use another variable to hold the excel file type(2007 or 2003)
4. Use two foreach loop containers one for 2007 workbooks and the other for 2003 workbooks
5. Iterate the two foreach loop containers through the array variable you have created earlier
6. Create a Data flow task inside each foreach loop container to collect data from each excel file.

I explained it step by step in my blog:
http://codewindow.blogspot.com/2009/11/exporting-data-from-multiple-workbooks.html

---

Thanks Dileep

Answer 15

Hi Dileep,

I was going through your blog above. The problem is that I am not able to follow the script because its not in VB script.

It will be really helpful if you can convert it to vb script.

Thanks a lot Dileep.

Thanks
Partha