Importing Data from Multiple workbooks to SQL using dtsx package RRS feed

  • 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

    Friday, October 30, 2009 7:59 AM

All replies

  • are sheets having specific names or they also randomly named?

    Thanks Dileep
    Friday, October 30, 2009 8:08 AM
  • Hi,

        The follwing link will help you 
    Friday, October 30, 2009 8:11 AM
  • 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/
    Friday, October 30, 2009 8:17 AM
  • 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.
    Friday, October 30, 2009 8:37 AM
  • 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
    Friday, October 30, 2009 8:44 AM
  • 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.

    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
    "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

    Friday, October 30, 2009 9:00 AM
  • Hi Dileep,

    Thanks for your quick response.

    The sheets are randomly named...

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

    Friday, October 30, 2009 11:04 AM
  • 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

    2nd Wookbook names under DATADUMP folder --->         May09B

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


    Monday, November 2, 2009 6:43 AM
  • 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
    Monday, November 2, 2009 6:59 AM
  • 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.


    Monday, November 2, 2009 8:22 AM
  • 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
    Monday, November 2, 2009 10:35 AM
  • what is the version of excel you are using is it 12.0(office 2007) or 8.0(office 2003)?
    Thanks Dileep
    Monday, November 2, 2009 11:47 AM
  • 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.


    Monday, November 2, 2009 1:38 PM
  • 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:

    Thanks Dileep
    • Proposed as answer by Dileep.Y Wednesday, November 11, 2009 6:05 AM
    Wednesday, November 11, 2009 6:03 AM
  • 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.

    Thursday, November 12, 2009 7:48 AM