Monday, March 12, 2012

Package Configuration

Here is the scenario we are

trying to use; (it a bit long winded please bare with me)

There are 2 Packages Parent

and Child. Both have 2 Connection Managers (��Configuration�� & ��Data Connection��).

The ��Data Connection�� has an invalid connection set to start with this is so

that we can deploy this to multiple places and use a SQL table to hold the

configuration.


At runtime ��Configuration�� is

set to a valid SQL2005 database connection via the command line or in BI but ��Data

Connection�� is left invalid.


The Package Configuration point

to an SQL server using ��Configuration�� this has the connection string for the ��Data

Connection�� Connection Manager (a valid connection string). The package errors

with can not acquire connection.


The first task is to put the

Connection string of the Connection manager ��Configuration�� in to a variable.

Then the child package is called.


The Child package has the same

2 connections ��Configuration�� & ��Data Connection�� both are Invalid at this

time.


1) We use the Package

Configuration to populate a variable from the parent package which holds the ��Configuration��

connection string.

2) We use a Package

Configuration to populate then Configuration�� connection from the parent

package which holds the ��Configuration�� connection.

3) We use a Package

Configuration to populate the ��Data Connection�� Connection Manager using the ��Configuration��

connection. Which should be Set via step 2 here.


The First task on the child

package has a bit of script to check all the connection manager return a valid

connection to make sure database are up and running before connection. This

fails at the moment return in an error.


We want to use the same code

in multiple environments and as such don��t want to make code changes that are really

only environment changes.

IE connection string to

servers. I can��t seem to see anything saying we are doping anything wrong but if

the connections are all set to a valid connection before running this seems to

work. When invalid connection are used this seems to fall over.

Peter you might be seeing the result of a known issue. When you define a package configuration using 'Parent package variable' type; no matter in which order you place it; it will be executed last.

You can see the details here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126146

PeterCh wrote:


The Child package has the same 2 connections ��Configuration�� & ��Data Connection�� both are Invalid at this time.


1) We use the Package Configuration to populate a variable from the parent package which holds the ��Configuration�� connection string.

2) We use a Package Configuration to populate then Configuration�� connection from the parent package which holds the ��Configuration�� connection.

3) We use a Package Configuration to populate the ��Data Connection�� Connection Manager using the ��Configuration�� connection. Which should be Set via step 2 here.

With this bug, your 2nd step occurs last; so your 3rd step would try to use the invalid connection string defined in the package; in consequence the configuration will not be actually performed.

Just to check, when running the packages in debug mode in BIDS, go to the progress tab and see in which order the package configurations are performed.

As a work around and since this issue only affects the behavior of ��parent package variable�� configuration type; instead of sending the connection string of ��Configuration�� via command line when calling the parent package; you can try to place it in a environment variable or file and then create a configuration on every package. Then create additional package configurations for the remaining connections managers using ��configuration�� connection manager.

I hope this help

No comments:

Post a Comment