How to manage different input (Excel files) format

Hi all,

I have created a package which import data from excel file and do some technical & business validation on the data. My package has about 20 control flow items. Now I'm asked to handle a second (and probably more in the future) excel file format (columns name are different, some fields are murged in one single column...).

I definitely don't want to create a different package for each excel file format. But I can't find a way in the control flow to execute a particular DataFlow in one case and another DataFlow in other cases. Typically I would like to evaluate an expression an depending on the result execute a DataFlow or another one. Even in a given DataFlow I cant find a way to have a condition and process different Excel Source depending on an expression result. Or it would be good if I could say to my Excel Source to discover the columns name and types at runtime and let me manage the columns manually in the data flow. Is that possible ? I know SSIS manage metadata on the columns based on the data source is there any way to manage the metadata manually ? I coulnd't find anything about that in BOL.

I guess an easy workaround is to have a different package just to import the different excel files in a common staging table and each package calls a single package which contains all technical & business validation.

Any help will be appreciated.

Kind regards,

Sbastien.

Have you discovered the expressions on precedence constraints? They seems like ideal fit for your requirements.

Double click a precedence constraint line, select a condition and an expression.|||

Right! That's what I needed.

Thanks for your answer.

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

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

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

|||

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

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

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

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

|||

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

sql

how to loop through my workflow by looking at the file system?

Hi, not sure my subject title makes it clear what I want but here it is.

I have a workflow which basically looks at an excel file in a folder on the local drive and then does loads of stuff to it. Everytime I want to process a different excel file that is in the same location al I have to do is change the value of a single local variable, which is just the name of the excel file.

Is there a way to make this automatic? For exmaple....could I somehow put my whole workflow inside a loop that looks inside that local folder and one by one, get the name of the file, assigns the name to that global variable, and then runs the flow...and continues to do that until it gets to the end?

any help would be greatly appreciated...thanks!!!!

andy

Have you look at the ForEachLoop container in the control flow? you can use it to loop through each excel file in a specific folder.

|||awesome, thanks!

how to link excel sheet with Crystal Report

How can i be able to link excel file with crystal report
i tried OLE and was succeeded but with problem that it displays the cells lines that are not needed.

second thing i was trying to do it through dsn (tried all the 3 dsn types)
but when i connect crystal report with it using ODBC it shows me
"No Items found".

any suggestion ?when i link excel file with Crystal Report8.5
as an OLE i see some extra blank cells with it on the right side and when i take a print of it, these cells are also printed

i used crystal report control to connect vb with report
the code is here
cr1 is the Crystal Report Control's Name
Code:

cr1.DiscardSavedData = True
cr1.ReportFileName = "C:\myexcelreport.rpt"
cr1.Destination = crptToWindow
cr1.ReportSource = crptReport
cr1.WindowShowRefreshBtn = True
cr1.WindowShowExportBtn = True
cr1.WindowShowPrintBtn = True
cr1.Action = True



Is there any way to dislpaly only the desired area of the excel file in report ?|||Hey guys
Finally got the solution.............|||hi

do u know how to import the fields from crystal report to an excel spreadsheet

or

to import values resulting from a query to a spreadsheet?

Thanks.

How to limit number of connections in Analysis Services

Hi All,

Our users accesses the OLAP database using excel with large data reporting. When the number of connections reaches to 20-30, the generation of reports becomes very slow. My question is, is there a way in order to limit the number of connections that can access the olap, in this way we can use this approach to let user create their reports by batch with only let say 5 connections.

Thanks...

There is no property or knob allowing you to limit number of connections.

You can use different ways to scale your application. One: is to install several machines and configure them to run as NLB cluster. This way your users will be connecting to the same IP and NLB will distribute the load between several physical machines.

You can try and implement simple application based on the DISCOVER_CONNECTIONS request ( see ActivityViewer sample application for example of it) and starts cancelling any new connections after reaching some threashold.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

How to limit conditionally records exported

I apologize if I'm posting this question in the wrong forum.

I have a query that exports data from an Oracle database to an Excel spreadsheet. The application that executes the query is Computer Associates Eureka Report writer. I do not have direct access to the database or any of its objects. With the data in Excel, I run a VBA macro-driven inventory report. The Excel spreadsheet row limitation is not a problem presently, however, the query exports a lot of extraneous data that I don't need. I actually export about a dozen columns of data however I'm only listing 4 in the example below.

Example:

CaseNum ActionDate ActionType ComplCd
1029901 09/08/2006 F 0
1029901 09/11/2006 C 0
1029901 08/18/2006 C 1
1029901 08/17/2006 F 1
1029901 08/01/2006 F 1

When the ComplCd = 1 I only want the query to export one row of data for each CaseNum. Is
there a way to code my query to accomplish this?

ugabulldog

Depending on the version of Oracle server, you can use one of the queries below:

select CaseNum, ActionDate, ActionType, CompICd

from (

select CaseNum, ActionDate, ActionType, CompICd

, ROW_NUMBER() OVER(PARTITION BY CaseNum, CompICd ORDER BY ActionDate DESC?) as seq

from tbl

) t

where (CompICd = 0)

or (CompICd = 1 and t.seq = 1)

-- or

select CaseNum, ActionDate, ActionType, CompICd

from tbl

where CompICd = 0

union all

select CaseNum, ActionDate, ActionType, CompICd

from tbl t1

where CompICd = 1

/*

Assumes that ActionDate is unique for each CaseNum, CompICd combination.

Else use some other appropriate column(s)

*/

and ActionDate = (

select max(t2.ActionDate)

from tbl as t2

where t2.CaseNum = t1.CaseNum and t2.CompICd = 1

)