Ben Sullins Tech

In SSIS 2005 a single config source (XML or SQL Server) with many connection strings could be used to setup the connections for multiple packages even if those packages did not contain all of the connections specified in the config source. In this scenario the native package configurations would read the source and attempt to apply connection strings for all those specified in the source. In cases where the connection did not exist in the SSIS package a warning would be reported however, these warnings could be ignored by setting the ‘SupressConfigurationWarnings’ property to ‘True’ within the package. This configuration strategy worked well for me in past experience since I would only have to manage the connection strings for almost all packages within a single config location. This method however, does not work in SSIS 2008

Below is the solution I decided on however I am not totally satisfied with it. If you are also running into this issue and have something to share please comment below. Thanks!

Variable Approach
1. Inside the package there is a variable for every connection manager that will contain it’s connection string
2. The value for these variables will be derived from a package configuration pulling from a SQL Server source
3. Connection managers all are then set by an expression assigning the value of the variable to their connection string property

Benefits
1. A warning (versus error) is thrown when there is a variable in the database not found in the package that can be ignored by updating the ‘SupressConfigurationWarnings’ property to true. This is also set by another configuration.
2. A single place to manage all connection strings for all SSIS packages

Cons
1. Extra steps are involved when adding new connection managers to a package

Below are the results from my tests using the same strategy in SSIS 2005 and SSIS 2008.

2005 Output

Warning: 0×8001F02F at Package: Cannot resolve a package path to an object in the package “.Connections[Conn2].ConnectionString”. Verify that the package path is valid.
SSIS package “Package.dtsx” starting.
SSIS package “Package.dtsx” finished: Success.

2008 Output

Error at Package: The connection “Conn2″ is not found. This error is thrown by the Connections collection when the specific connection element is not found.”

Thanks to “jaegd” for his post here: http://forums.microsoft.com/… on how to accomplish this. Below please find some more details along w/ a example which can be downloaded here (ssis-2005-excel-2007-source-example).

This is an interesting challenge that I recently explored during a forum discussion on SQLServerCentral.com. The situation in which the user presented wasn’t exactly ideal so I will first present the other two options I proposed besides the final one that appears to work for him in his environment.

Challenge: When extracting data from an Excel source I would like to add a row number.

Solutions:

1. Include an identity column in the staging table which would add the row number for future data flows that consume the data.
2. Write the Excel data to a transient table (which has an identity column) that gets truncated at the beginning of the package (resetting the identity column).

Both of these options are expensive I/O operations that would slow things down. Another option presented by Jack Corbet was to use a script task to increment a package variable row by row and adding that to the output.

The final solution I proposed seems to be suitable for this case and I may look to implement this more in my future SSIS requirements. Here goes:

1. Added an OLE DB Source to my data flow.
2. Selected a SQL 2005 DB Server for my connection manager.
3. Chose ‘SQL Command’ as my Data access mode.
4. Used the RowNumber() function combined with the OPENROWSET() connection method to compose a query which pulled all the data in from the Excel sheet adding in the RowNumber.

In order for this to work your SQL 2005 Surface Area Configuration must allow the OPENROWSET() connection method and you’ll most like want to define this SQL Statement dynamically through a package configuration or some other method.

Here is the query:

SELECT

                ROW_NUMBER() OVER (order by [VendorAccountNo]) AS [RowNumber]
        ,       [VendorAccountNo]
        ,       [LocalAccountNo]
        ,       [CompanyCd]
        ,       [CUSIP]

FROM

        OPENROWSET(
                'Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=C:\temp\MySheet.xls;HDR=0; IMEX=1;',
                'SELECT * FROM [Sheet1$]'
        )

I’ll probably write more on this later as to which method provides the best performance and maintainability but for now feel free to comment on what you think…

Cheers!,
Ben