Deploying packages with SQL Server based configuration

Hello,

We have been conducting some testing regarding package deployment and SQL Server based configuration. It seems there is a problem that was documented in an MS Feedback entry (

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126797

)

I searched for a viable answer, work-around, or hotfix that would address this issue but found none.

The jist is that a package that uses SQL Server based configuration must have an xml-based configuration entry to "re-point" the SQL Server based configuration connection manager to the deployment target server. We cannot use environment variable or registry as this is against internal policy. The problem is that even though the xml based config is specified first in the list of package configurations it does not get applied first at run-time. So, when the package runs from a SQL Server Agent job the package's connection manager for the configuration entries is not updated.

The package runs correctly through BIDS. You can change the connection string in the .dtsConfig file and the SQL Server based package configuration is obtained from the correct source.

Environment is SQL Server Enterprise Edition 64-bit w/ SP1, Windows Server 2003 Enterprise Ed. 64-bit.

Does anyone have any experience with this issue? Know any hotfixes, other work-arounds?

Thanks in advance!

In terms of environment, 64 bit SSIS (w/ post SP1 hotfix) and Windows 2003 x64 SP1, we have the same setup. I am also running this exact scenario and have no problem repointing the SQL server Configuration's connection manager's connection string via a previous direct XML configuration.

So, the only thing I can tell you is to look for configuration warnings (unable to read configuration), which is a dead giveaway that you'll be pointing back to your design time config. (e.g. Warning with a message "Unable to read configuration file...").

Another diagnostic is to print out ("fire info events") your connection strings, presuming these are part of your SQL Server configuration, as a preliminary script task. This information will tell you right away if configurations are being properly overlaid.

Public Sub Main()
Dts.TaskResult = Dts.Results.Success
Dim conn As ConnectionManager

For Each conn In Dts.Connections
Dts.Events.FireInformation(0, String.Empty, String.Format("Connection: {0} DataSource: {1}", conn.Name, conn.ConnectionString), String.Empty, 0, False)
Next conn

End Sub|||

I ran accross that as well and thought I was sunk. Turns out it must be an old error and fixed at some point. (I'm running 2005 SP1).

We use an XML configuration in C:\MSSQL to define where the Configuration connection points to and pull all other entries from Configuration source. We just deploy an XML file to each developer desktop (that points to development) and on each of the servers (pointing to themselves). We can move packages from development to test to production with no issues as well as run them in debug mode on our local desktop. In the off chance you HAVE to run a production job in debug from your desktop, just replace your local XML file (and make sure you run with appropriate permissions).

To be more clear, our configuration looks like:

XML Configuration File
SQL Server

This seems to work well for us. Hope this helps,

Larry C

|||

Larry & jaegd,

Thank you for replying to this thread.

Ok, the package has two connection managers, 1. ConfigurationConnection used for the SQL based configuration connection 2. DW_ETL is used for the DataFlow and other tasks in the package.

Using the script from jaegd to log the connection strings for all of the connection managers in the package I have found that the connection managers' connection strings are being changed appropriately to their configured values as stored in the SQL based configuration.

However, I am seeing, and this is the observation that caused me to post here, that a package variable User::TestConfig is not being changed appropriately. The script is listed below. The variable User::TestConfig is specified to get its value from the SQL based configuration.

Am I missing something here, shouldn't the variable's value be changed to what is specifed in the confguration table?

Public Sub Main()

Dim conn As ConnectionManager

Dts.Events.FireInformation(0, String.Empty, String.Format(Dts.Variables("User::TestConfig").Value.ToString()), String.Empty, 0, False)

For Each conn In Dts.Connections

Dts.Events.FireInformation(0, String.Empty, String.Format("CONNECTION: {0} DATASOURCE: {1}", conn.Name, conn.ConnectionString), String.Empty, 0, False)

Next conn

Dts.Events.FireInformation(0, String.Empty, String.Format(Dts.Variables("User::TestConfig").Value.ToString()), String.Empty, 0, False)

Dts.TaskResult = Dts.Results.Success

End Sub

-Eric

|||

Wanted to update this thread.

I have confirmed, that a user variable that has its Value stored in configuration is not being properly updated from SQL based configuration. This happens when the package is run via a SQL Server Agent job. However, the Value is changed correctly when the package is run interactively through BIDS.

Is there some sort of caching mechanism in SSIS (just a swag) that is causing the variable's Value to NOT change even though it is set up to be configured from SQL based configuration.

As a reminder other information such as connection string which are stored in SQL based configuration are changing based on their configured values, this happens correctly in a SQL Agent environment as well as BIDS.

Thanks!

-Eric

|||

Larry,

I am curious, what environment are you running on where the xml and SQL based configuration is working properly? The following is my environment.

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

(No column name) (No column name) (No column name)
9.00.2153.00 SP1 Enterprise Edition (64-bit)

I am asking because using xml config to point the table-based configuration's connection string is not working for me. Are there any gotcha's regarding the connection managers, or the job step configuration, or....

In the package I specified the order of the package configurations with the xml config listed first, then the sql based config. In the job step I had added the location of the xml-based configuration in the configurations tab. I did not have any check boxes checked on the Data Sources tab in the job step. The configuration data itself was correct. But, the sql based configuration was not being pointed to the connection string that is specified in the xml config. The logging output showed that the sql-based connection string was changed, however the values that were specified in the sql-based configuration were not being updated.

Any tips or help would be most appreciated.

-Eric

|||

Eric,

Here's the values you requested.

(No column name) (No column name) (No column name)
9.00.2047.00 SP1 Enterprise Edition (64-bit)

As a validation I did the following:
I created an SSIS package
I created a Connection called Configuration using integrated security pointing to the Development server and SSIS configuration database.
I created a variable called Test and set the value to Test
I Enabled configuration
I created an XML configuration file at C:\MSSQL\Configuration.xml (I'd recommend using a default extension)
I put Configuration data source in the XML file
I named the Configuration XML
I added a new SQL Server Configuration, specified server, table, and Key
I chose to store the configured Value of the Test variable
I enabled Logging to a file C:\MSSQL\Logs\Sample.log
I added a Script Task
I set ReadOnly variable to Test
In Design Script I put the following

Public Sub Main()

Dts.Log(Dts.Variables("User::Test").Value.ToString, 0, Nothing)

Dts.TaskResult = Dts.Results.Success

End Sub

I enabled logging for the Script Task and enabled the ScriptTaskLogEntry

Important items:
The XML configuration file must be on a local hard drive
The developer will need an XML configuration file pointing to development
Each server will need an XML configuration file pointing to itself and the configuration database.
The database and table the configurations are stored in, must be what is referenced in it's local XML configuration file.
When creating an XML configuration for a new package Reuse the existing XML file.

The output from running on my desktop and the development server was:
User:ScriptTaskLogEntry,IT-HQ4Z971,CHARLOTTE\lcharlton,Script Task,{E14E7D5F-F31C-4717-A7E2-31FA3A12E157},{2179265C-2855-4A02-962F-4D1D5E2F27E0},11/1/2006 2:22:47 PM,11/1/2006 2:22:47 PM,0,(null),Test

The output from running on the production server was
User:ScriptTaskLogEntry,xxx-xxx-xx,CHARLOTTE\xxxxxxxxxx,Script Task,{E14E7D5F-F31C-4717-A7E2-31FA3A12E157},{FC1A8FF0-B4D4-4604-98C6-F5BD60C2DEE3},11/1/2006 2:27:57 PM,11/1/2006 2:27:57 PM,0,(null),Production

The configuration connection is getting reset correctly and the variable from configuration is comming from the production server. We have equally good luck setting other connections to different databases. I even have a SSIS package that updates the configuration entries for another SSIS package and then executes it, allowing us to iterate through a list of servers collecting information. This works as expected in Development, Test, and Production with the only thing needing to change being the configuration values in the SSIS database.

|||

Larry,

Thank you for the time and response in the validation of this. The description of your implementation including the "Important items:" helps me validate that we are attempting to do the same thing.

So, your version is the SQL Server 2005 Service Pack 1 build, x64, while my version is the Cumulative hotfix package (build 2153) for SQL Server 2005 build, x64.

I thought I recalled that this issue was fixed in SP1. I am wondering if this was "un-fixed", for lack of a better word, in the cumulative hotfix package?

I am thinking this may be the case.

Any ideas beyond taking this to PSS?

-Eric

|||Sorry, nothing here. PSS is probabbly your best bet.