Moving thread to the Intergration Services forum. They may be able to help out.
|||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 sheets to retrieve the data and save it in a database table. I know how to retrieve the data from one sheet, but I don't know how to do it for several sheets. Any ideas?...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
|||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
|||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 to add some columns and I have the ones I need...but I don't know how to do the next part.
|||Is there a link where I can see the use of the Foreach item enumerator?|||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 to add some columns and I have the ones I need...but I 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
|||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 Server table 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
|||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 variable to 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
|||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
|||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 package to write into the same file but different sheet each time. For that, try to create the 100 sheets in your excel destination file; add a variable to the package called, let's say DestinationSheet, and for each iteration change the value of that variable to one of the 100 sheet your destination file has (according with the set 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
|||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 software artifact 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
|||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, then I can fill that same range. Ideally, the worksheet name and workbook name would both come from a variable.
Thanks,
Andre
|||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 location and then you just create a new copy to be used as the target every time the package runs.
I hope this takes you one step further on all this.
Rafael Salas
|||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 to Excel issues raised in this thread.
Regards,
Andre Chan
No comments:
Post a Comment