how to loop through several excel sheets in a file in integration services
Hello, I'm new at Integration services and I have an excel file with information in several worksheets. I want to loop through some specific sheetsto retrieve the data and saveit in a database table. I know how to retrieve the data from one sheet, but Idon't know how to do it for several sheets. Anyideas?...I would appreciate any help.
October 13th, 2006 12:39am

Moving thread to the Intergration Services forum. They may be able to help out.
Free Windows Admin Tool Kit Click here and download it now
October 13th, 2006 7:39am

Trisha1802 wrote:Hello, I'm new at Integration services and I have an excel file with information in several worksheets. I want to loop through some specific sheetsto retrieve the data and saveit in a database table. I know how to retrieve the data from one sheet, but Idon't know how to do it for several sheets. Anyideas?...I would appreciate any help. Unfortunately its not possible to loop through sheets unless you know the names of them and you know that they won't change. If those conditions are satisfied then you can use the ForEach item enumerator in the Foreach Loop. An Excel Sheet enumerator would be a nice addition. Perhaps you could request it at Microsoft Connect? -Jamie
October 13th, 2006 8:41am

Interesting situation. In SSIS Excel Source BOL section (http://msdn2.microsoft.com/en-us/library/ms141683.aspx) there is a link "HOWTO: Use ADO with Excel Data from Visual Basic or VBA" that has some sample code to "Enumerate Tables and Fields and Their Properties". I wonder if you could use some of that code inside of a script task to loop through all the sheet in your file and write their name in a flat file. Then it would be pretty easy to use a ForEach loop container with a data flow that connects to the excel file and runs a query like 'Select * from <SSIS variable with the sheet name>' I have no idea how to write such script component and don't know how feasible that could be; so you do the research. Rafael Salas
Free Windows Admin Tool Kit Click here and download it now
October 13th, 2006 3:54pm

Thank you very much for your reply Jamie. I haven't used the Foreach item enumerator...could you please explain to me a little bit more how to pass the sheet name to the container?. I saw that I have toadd some columns and I have the onesI need...butI don't know how to do the next part.
October 14th, 2006 3:09am

Is there a link whereI can see the use of the Foreach item enumerator?
Free Windows Admin Tool Kit Click here and download it now
October 14th, 2006 3:12am

Trisha1802 wrote: Thank you very much for your reply Jamie. I haven't used the Foreach item enumerator...could you please explain to me a little bit more how to pass the sheet name to the container?. I saw that I have toadd some columns and I have the onesI need...butI don't know how to do the next part. You have to type them in at design-time basically. That's why I said you need to know them. Its not particularly clever or dynamic but it'll work. -Jamie
October 14th, 2006 4:53am

Just to add my 2cents to the request list -- I am trying to export from SQL Server to a single workbook with about 100 worksheets. I was hoping to be able to loop through a two-column recordset from a static SQL Servertable with the names of the worksheets and the SQL command to extract/export data into each worksheet. The name and path of the workbook remain constant. However, it appears that the "Excel Destination" Data Flow does not accept variables for either the name of the worksheet (or workbook). That means I have to hard-code everything related to the export to 100 worksheets/workbooks. I was hoping to avoid needing to create 100 little export file tasks in the package, but that appears to be my only recourse. Any other ideas? -Andre Chan
Free Windows Admin Tool Kit Click here and download it now
October 25th, 2006 10:41pm

You don't need to create 100 litle exports; you need to change the data access mode of your Excel Destination component to 'Table Name or View Name variable". Then you need to use a SSIS variableto hold the Excel sheet name you want to write into. You can use a forErachLoop container in the control flow to go over the 100 sheet names. Rafael Salas
October 25th, 2006 11:00pm

To clarify a bit more -- within my For Each loop container, I could always use Excel Destination task to write to a single "dummy" filename, and then use the File System task within the same For Each loop container to re-name the file to whatever I want (using my variable?). In this case, I end up with 100 workbooks rather than 1 workbook with 100 worksheets, but that is better than nothing. Note that I need these worksheets because they are referenced via VLOOKUP from another well-formatted workbook. The bigger problem I am having now is trying to populate these workbook/worksheets using a dynamic variable SQL command. I want to use a single stored procedure with specified parameters to fill each workbook/worksheet, to make it easier to manage in the database. These SQL commands are loaded into a SQL Server table. It seems built-in options in SSIS are to use a specific pre-defined, pre-created view or table name. But in this case then I need to create 100 different tables or views in the database, and that is what I am trying to avoid, because editing the logic would require updating all 100 views (and perhaps creating/updating all 100 tasks in the package if the view/table names change). -Andre Chan
Free Windows Admin Tool Kit Click here and download it now
October 25th, 2006 11:16pm

Andre Chan wrote: To clarify a bit more -- within my For Each loop container, I could always use Excel Destination task to write to a single "dummy" filename, and then use the File System task within the same For Each loop container to re-name the file to whatever I want (using my variable?). In this case, I end up with 100 workbooks rather than 1 workbook with 100 worksheets, but that is better than nothing. Note that I need these worksheets because they are referenced via VLOOKUP from another well-formatted workbook. The bigger problem I am having now is trying to populate these workbook/worksheets using a dynamic variable SQL command. I want to use a single stored procedure with specified parameters to fill each workbook/worksheet, to make it easier to manage in the database. These SQL commands are loaded into a SQL Server table. It seems built-in options in SSIS are to use a specific pre-defined, pre-created view or table name. But in this case then I need to create 100 different tables or views in the database, and that is what I am trying to avoid, because editing the logic would require updating all 100 views (and perhaps creating/updating all 100 tasks in the package if the view/table names change). -Andre Chan I see the difference now. I still think you can write all inside of a single workbook, and you are very close to make it. So, if you already succeed looping through the 100 data sets; then trick I think is to make the packageto write into the same file but different sheet each time. For that, try to create the 100 sheets in yourexcel destinationfile; add a variable to the package called, let's say DestinationSheet, and for each iteration change the valueof that variable to one of the 100 sheet your destination file has (according with theset of data you are processing of course).You will not use the FileSystemTask to rename any files; because you are writing into a single file. Rafael Salas
October 25th, 2006 11:35pm

Thanks, I did get my package finally to run, but I do have one last problem specified below in the last paragraph. As you might expect, my problems were somewhat "trivial" and/or only remotely related. First, I had to specify the worksheet name with $ attached at the end. Perhaps because I had renamed the worksheets within the workbook along the way? That's a softwareartifact that would be nice to clear up, as there was no helpful error message provided by SSIS, only that the "database" could not be found. That's where I got confused by the wording of the error message -- it never said "worksheet". Second, my source table data had columns specified as "varchar" but Excel Destination task widget only accepts "nvarchar". Changing the datatypes in the source columns to nvarchar fixed that problem. At least SSIS did give an obliquely useful error message in this case (but didn't explicitly tell me to go change the source table datatypes, only that varchar and nvarchar did not "match"). There was also a bit of necessary work-around because, within the ForEach container,in order to specify the source column mappings returned via my stored procedure from the "SQLCommand" variable, or for Excel to recognize its worksheet name/column mappings via its "WorksheetName" variable, I had to pre-define default values for the variables. It would have been nice (but quite advanced) for SSIS to anticipate the first row from the initial SQL recordset that fills the ForEach command. I have one last problem. If the Excel worksheet already contains data, SSIS appears to be insert new rows for the data (or append it to the existing data -- I haven't figured that out yet). Is there any way to get SSIS to clear the worksheet before dumping data into the worksheet. For example, when setting up a PivotTable or other query from within Excel itself, there are multiple options for completely clearing the existing data first, inserting new rows and clearing rows, or just inserting new rows. I would just like the data cleared, because I don't want to have any VLOOKUP references to this spreadsheet to get corrupted. Rright now, each worksheet has a few hundred rows, but the VLOOKUP references rows 2 through 10000 (ignoring header row), just to ensure it gets the whole data range. Thanks, Andre
Free Windows Admin Tool Kit Click here and download it now
October 26th, 2006 1:12am

The last bit of pre-processing I want to do in Excel, I know how to do in an Excel macro, but not in SSIS. Can anyone adapt this into an SSIS Script task? Here is the macro code snippet: Sub ClearWorksheets() With Application .Calculation = xlManual .EnableEvents = False Worksheets("MyData").Range("A:H").Clear .EnableEvents = True .Calculation = xlAutomatic End With End Sub After the range is cleared, thenI can fill that same range. Ideally, the worksheet name and workbook name would both come from a variable. Thanks, Andre
October 26th, 2006 3:56am

Andre, I actually don't know if SSIS can 'delete' or 'truncate' an excel sheet; What i know though, is SSIS is accessing the excel file using and OLE DB provider for Microsoft Jet; so each sheet is treated as table. With this said; do yourself a favor, research arround Microsoft Jet 4.0, its OLE DB provider and may be you will see your options around what you want to acomplish. that may help you also to understand the limitations of the data types. If truncating or deleting the existing rows before loading the new data is not possible; you could have an empty excel file with the required structure in a specifc locationand thenyou justcreate a new copy to be used as the target every time the package runs. I hope thistakes youone step furtheron all this. Rafael Salas
Free Windows Admin Tool Kit Click here and download it now
October 26th, 2006 3:57am

Hi Rafael, Trying to use a DELETE statement against the Excel file yields an error message (the syntax varies based on where I click OK), but generally all say something to the effect of "Deleting data in a linked table is not supported by this ISAM. (Microsoft JET Database Engine)". So my only recourse seems to be, as you suggested,to (1) create a "blank" Excel template with all the worksheets I need, (2) when my package runs, create a working copy of the template, (3) fill the copy with data on each worksheet, (4) archive the "published" Excel file, if it exists, and (5) copy/rename the working copy to the published filename. Thanks for your comments, your support and encouragement are appreciated; and if any Microsoft people are following this, it would be nice to address all of the SSIS toExcel issues raised in this thread. Regards, Andre Chan
October 26th, 2006 5:30am

You are very welcome. I am glad to help you. Rafael Salas
Free Windows Admin Tool Kit Click here and download it now
October 26th, 2006 3:04pm

Hi Andre, do you have a package I can examine? I've tried to do the same task but failed. 10x, Rouie. (you can send the dtsx file to rouie@mail.com)
March 29th, 2007 3:02pm

I made an example for those people that still need:http://biresort.net/blogs/pedrocgd/archive/2009/08/06/stepbystep-ssis-extract-data-from-multiple-excel-worksheets.aspxI hope this help.regards,PedroVisit http://www.pedrocgd.blogspot.com & http://www.BIResort.net - If your question is answered, please mark as answered.
Free Windows Admin Tool Kit Click here and download it now
August 6th, 2009 2:59pm

I made an example for those people that still need: http://biresort.net/blogs/pedrocgd/archive/2009/08/06/stepbystep-ssis-extract-data-from-multiple-excel-worksheets.aspx I hope this help. regards, Pedro Visit http://www.pedrocgd.blogspot.com & http://www.BIResort.net - If your question is answered, please mark as answered. The link is dead. I am looking for something similar can anyone help with this?
April 14th, 2012 10:09am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics