Saturday, November 10, 2012

SSIS: How to loop through Multiple Excel sheets and load them into a SQL Table?

Microsoft Excel spreadsheet is use by many individuals and organization, business to store data and perform calculation on this data. Reading and Writing data into Ms-Excel using SSIS is very common. Ms-Excel spreadsheet can store data into multiple sheets. Reading data from these sheets and storing it into a SQL table is pretty easy with SSIS. In this post we will learn to read data from multiple sheets and store it into a SQL table.

For demonstration purpose let us consider following Ms-Excel spreadsheet with data in three different sheets. Each sheet contains two colum data - DeptCode and Deptname.

Let us create a SQL table which will store the data from all three sheets of Ms-Excel.

CREATE TABLE [dbo].[tblDepartment](
    [DeptCode] [varchar](50) NOT NULL,
    [DeptName] [varchar](50) NOT NULL
) ON [PRIMARY]

Let us create the SSIS package now. The very first thing we did is to create a variable with the name “SheetName”. The default value in this variable is name of first sheet i.e. Sheet1$.

In the control flow tab we have added two tasks – one Foreach Loop Container and one Data Flow Task.

Foreach Loop Container will loop through each of sheet and perform Data Flow Task activities. On Foreach Loop container editor dialog box we have selected Foreach ADO.NET Schema Rowset Enumerator and in Connection we have selected New Connection.

From the Connection Manager list we have selected Microsoft Jet 4.0 OLE DB Provider.

We have selected the Ms-Excel spreadsheet as Database file name

From the All properties tab we have setup Extended Properties value to Excel 8.0

From the Schema list we have selected Tables.

From the Variable Mappings tab we have setup the variable mapping with user defined variable "SheetName" to index 2.

On Data Flow Tab we have following three controls.

Excel Source controls read the data from each of the sheet. On Excel Source we have setup the Data access mode to Table name or view name variable. We have selected the variable name as SheetName, we have defined this variable earlier.

From the Columns we have selected the two output columns – Deptcode and DeptName.

Data Conversion control convert the data type from Unicode to Varchar as the data we read from Excel is treated as Unicode.

OLE DB Destination controls load the data into SQL table. We have mapped the derived columns with SQL table.

After setting up the SSIS package, I run the SSIS pacage and it runs successfully,

I checked the SQL table and all data from all the sheets were loaded successfully.

25 comments:

  1. Hi Vikas,
    Need ur Help.I am stuck at a place where I have to Browse and Pick DataBase FileName.
    I do not see the Excel source file when I browse it .When I copy The Excel file Path Here Like
    C:\BI_Inventory\Inventory_SSIS\EXCEL\MyStuff\LoopExcel\A.xlsx
    and after checking "testConnection" I get error i.e Error in Initializing Provider.
    FOR YOUR INFO
    If I create simple Package to Load Excel file in SQL table and Check Excel connection Manager I see Below detail
    -------------
    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\BI_Inventory\Inventory_SSIS\EXCEL\MyStuff\LoopExcel\A.xlsx;Extended Properties="Excel 12.0 XML;HDR=YES";
    ------------
    Plz help

    ReplyDelete
  2. Sounds like a driver or machine issue. Do you have 32 bit or 62 bit machine? What version of excel you are trying to read 2003 or 2007? I would suggest you to provide these details along with screen shots and put this question on MSDN SSIS forum http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/threads/ for a faster reply.

    ReplyDelete
  3. I am getting a error that "A table name has not been provided"

    could u help me out

    ReplyDelete
  4. Where are you getting error? In which, step? Are you able to setup the package as described above.

    ReplyDelete
  5. Hi Vikash,
    Thank you for this wonderful post.I used similar appraoch for an excel workbook with 26 sheets. I changed the index for enumerator to 26 and it fails all the time with the error"
    The enumerator failed to retrieve element at index"9"

    ForEach Variable mapping number 1 to variable "User:Sheetname" cannot be applied.

    I also checked sheet9 for any possible NULL values but didn't help.

    Any idea what I might be doing wrong here?

    ReplyDelete
  6. @Sdave

    You do not need to change the enumerator. I tried to recreate the scenario with 30 sheets but I did not get that error message. One possible reason could be data in the Sheet could be null or data columns may not in proper order. please check it once.

    ReplyDelete
  7. Hi Vikash!.Thanks for ur post.In my case i have 44 sheets named after different molecules.Column names for all the sheets are same.To distinguish the data of these sheets i think to add the sheet name as a column in my SQL Table.Is it achievable or what is the workaround
    -----------------------------
    Thanks
    Pratik

    ReplyDelete
  8. Dear Pratik,

    If you want to store Sheet name you have add a derived column between Excel source and Data Conversion. In the Derived column component add a new column and set its value to variable @[User::SheetName]. This column will store the sheet name. In the OLE DB Destination you can map the SQL Table column in which you want to store sheet name to this new Derived column.
    Do let me know if you face any issue.

    Thanks for your comment and considering me to consult for this problem.

    ReplyDelete
  9. Hi Vikas,

    Thanks for good article. But, for me data gets loaded twice always. Let me know what could be the reason?

    Regards,
    Naveen

    ReplyDelete
  10. @Naveen: Please check your source folder, you mayhave multiple files with same set of data. The other possibility is you are running the package again and again and data is getting added into the table and causing ambiguty in the table. I would sugges to delete all the records and run the SSIS pacakage once.

    ReplyDelete
  11. Hi Vikas,

    could you please help me out:

    I created the package successfully but it is not looping through and moreover i am not getting any error too.

    ReplyDelete
  12. Hi vikas,

    Could you please help me out, I created the package successfully but it is not looping through.

    Moreover i am not getting any error.

    ReplyDelete
  13. After setting up the SSIS package, I run the SSIS pacage
    BUT only FOREACHLOOP is showing executred successfully.
    DFT is not running with foreachloop.
    I dont understand why DFT is not running.

    ReplyDelete
  14. After setting up the SSIS package, I run the SSIS pacage
    BUT my package only executed FOREACHLOOP.
    DFT is not running.
    I ran it again n again but DFT is not running.
    only FOREACHLOOP excuted with green color.
    plz tell me wht DFT is not running it shud run right?

    ReplyDelete
  15. This comment has been removed by the author.

    ReplyDelete
  16. This comment has been removed by the author.

    ReplyDelete
  17. @Sukhjeet: Please send me more details to figure out your problem. What version of SSIS you are using, what version of excel file you are using?

    ReplyDelete
  18. @Jagir: Please send me more details to figure out your problem. What version of SSIS you are using, what version of excel file you are using?

    ReplyDelete
  19. @vikash-
    I found the problem.
    issue was in xl version. (used .xls) instead .xlsx.
    jet 4.0 ole db provider does not allow .xlsx connection.
    i am fine now.
    if there is any sol for .xlsx plz let us know

    ReplyDelete
  20. Hi Vikas,
    Thanks for your post but I want to skip First 3 & last 3 rows from all the sheet as header start from 3rd row.

    ReplyDelete
  21. Hi Anuraj,

    To skip 3 rows from top you can follow the approach mention here

    http://www.singhvikash.in/2011/11/ssis-read-and-export-excel-data-from.html.


    ReplyDelete
  22. Let us say, in one of the sheets, a column is missing or the name of the column is incorrect. We need to ignore this sheet and proceed loading other sheets.
    Is there any way other than script component that it can be achieved??

    ReplyDelete
  23. hi Vikash,
    why are we setting index to 2 for sheetname in variable mapping
    Preetha

    ReplyDelete

Please feel free to write your Comment here

Popular Posts

Real Time Web Analytics