x

Passing dtsconfig from Master to Child packages

Hello all, I am new to SSIS and have inherited a solution which has roughly 30 packages in a solution. I have lost a week trying to resolve this using the power of Google and ameteurish experimentation. I realise I could spend more time eliminating possibilities but was hoping someone might help me shortcut that a bit.

Problem; I am trying to create a master package for a solution containing about 30 packages and over a week have failed to be successful. I want to pass the location of the dtsconfig file from the parent to the child but the child ignores it. From what I have read, I think this should be possible but just can't make it work.

Scenario. I have a solution which is housed in C:\Development\SSIS\JPMC POC DEV1. the solution has roughly 30 packages that all use the same dtsconfig file housed in the above path. They all leverage tables in the same DB JPMC_POC_DEV1. To develop changes, I copy the entire solution to a new folder e.g. C:\Development\SSIS\JPMC POC TESTING and create a copy of the DB calling it JPMC_POC_TESTING All 30 packages have the same connections so I edit the new dtsgconfig file in two places to point the Package connections to the new JPMC_POC_TESTING database. Then I edit each of the 30 packages to point it to the newly edited C:\Development\SSIS\JPMC POC TESTING\JPMC POC Configuration.dtsConfig All the packages are currently run individually. When I am done testing I have to repeat the above to put it back in the original folder.

ISSUES; What I think I want to do, is create a master package to run all the 30 packages and pass them the location of the dtsconfig file. My intention being that I only have to change the dtsconfig location once in the parent when I am moving between C:\Development\SSIS\JPMC POC DEV1 and C:\Development\SSIS\JPMC POC TESTING.

So Currently I have a master package (MASTERPKG) and have given it a string variable called CONFIGPATH at the package scope level. I have entered "C:\Development\SSIS\JPMC POC TESTING\JPMC POC Configuration.dtsConfig" in the variable. In MASTER there is just one "Execute package task" which calls one of the packages called "DIM_HEADCOUNT". In DIM_HEADCOUNT package, I have created a string variable also called CONFIGPATH and set the Package Configurations to use "Parent Package Variable" and use the Parent CONFIGPATH to set the Value property of the DIM_HEADCOUNT CONFIGPATH variable. When I execute the task from MASTER, the child package completes using whatever connections were already defined and does not use the new connections defined in the parent dtsconfig.

OBSERVATIONS; In the solution there are 2 datasources ("Target OLEDB JPMC DW" and "Target .Net JPMC DW") and every package in the solution has 2 Connection Managers with the same names.

QUESTIONS; What am I doing wrong? Is what I am doing possible? Do the Solution Datasources and Package Connection Managers duplicate each other and/or override each other in some way causing it to appear that the config file is being ignored?

Thanks. Dan
more ▼

asked Oct 17, 2011 at 03:03 AM in Default

dimsandwich gravatar image

dimsandwich
1 1 1 2

(comments are locked)
10|1200 characters needed characters left

6 answers: sort voted first

It sounds like you are wanting to implement indirect package configuration. Check out this link where this is discussed:- [link text][1]

[1]: http://sqlscape.wordpress.com/tag/direct-vs-indirect-configuration/
more ▼

answered Oct 19, 2011 at 08:02 AM

philnolan gravatar image

philnolan
322 2 2 3

(comments are locked)
10|1200 characters needed characters left

Thanks for the link. After reading it I went here http://msdn.microsoft.com/en-us/library/ms345179.aspx and followed the instructions but it doesn't behave as I expect (maybe my expectations are wrong).

I created a variable called ConfigPath in the child package and ConfigPath in the parent. They are both set as string variables. The Scope of the parent variable is at that package level. and the scope of the Child is at the package level.

I modify the package configuration of the child to read ConfigPath. Close/Save Child.

The Parent variable is populated with C:\Dev\SSIS\Config\TEST.dtsConfig. I set up a connection to the child package and add an EXECUTE PACKAGE task to a Sequence Container in the master.

The only things that the dtsConfig sets are connection info to the database (lets call it DEV4).

I open the master package and can see that the connections are pointing to DEV4

I execute the container and the child package executes but it's connections are not inherited from the parent (they point to DEV2 database). The Locals window is blank. I stop debug and I check the package configuration and it is pointing to ConfigPath variable, the variable is empty.

I expected the child to inherit the ConfigPath variable from the parent, read the config, inherit the DEV4 dataconnection settings and run. Instead, the child totally ignores the parent config file.

What am I doing wrong?
more ▼

answered Oct 24, 2011 at 10:12 AM

dimsandwich gravatar image

dimsandwich
1 1 1 2

(comments are locked)
10|1200 characters needed characters left

Maybe I'm being too verbose. I want to move an SSIS solution to different folders on a server. The folder containing the solution also contains the dtsconfig file. If I move the folder I then have to open 30 packages in the solution and change the config file path. I want to change it once. I can't use environment variable or registry entry. And I can't make the Parent package variable option work as the child packages ignore the parent config variable. Help!!

Dan
more ▼

answered Oct 26, 2011 at 11:56 AM

dimsandwich gravatar image

dimsandwich
1 1 1 2

(comments are locked)
10|1200 characters needed characters left

I think I understand what you are trying to accomplish, and I can understand the confusion on how to make it happen. SSIS has a nasty habit of convoluting things sometimes. I think the confusion is happening when you create your configuration, and choose "Parent Package Variable", and the scoping of that variable. The Parent Package Variable needs to be scoped to the Execute Package container and not the package itself. I just ran a test and proved the point.

  1. Create a package variable scoped to the Execute Package Task, in the parent package.
  2. Create a Parent Package Variable configuration in each child package that utilizes the parent's task scoped variable's value and assigns it to the child package variable's value.
  3. Create expressions that will value the parent package variables with one "master_config" value.
Sounds a more difficult than it is, really. It is all in the scope...
more ▼

answered Oct 26, 2011 at 02:10 PM

the_SQL gravatar image

the_SQL
102 1 1 3

(comments are locked)
10|1200 characters needed characters left

Excellent, yes you have understood perfectly and explained it really clearly. The scoping thing had passed me by so I was very excited. Sadly, I still get no joy, so if you can bear it any more, here's my setup;

"Master Package" contains just one "Sequence container", the sequence container contains just one "execute package" task. The task executes a package called "Dim Build".

Variable setup Child "Dim Build" has empty string variable called "ConfigPathExec" set to EvaluateAsExpression = False, the variable is scoped at the package level and configuration is set to select "ConfigPathExec" from parent. I've quadruple checked spelling and case.

Parent "Master Package" has 2 new variables now.
"ConfigPathExec" string variable, EvaluateAsExpression=True @[User::ConfigPathMaster], scoped at the Execute package task level. "ConfigPathMaster" string variable containing C:\theconfig\Dev5 config.dtsConfig, scoped at the "Master Package" level.

I close all the packages in Visual Studio, right click on the master package and "execute package", it opens, then child package "Dim Build" opens, prompts for password, warns about long text strings, then runs to completion.......AGAINST THE WRONG DATABASE!!!! arggghg

The "Master Package" connections in connection manager point to database DEV5 (as per dtsConfig file) but the child just insists on using DEV2 which is where is was previously set, ignoring the passed down config file.

So I tried it adding in an intervening ConfigPathSeq variable at the sequence container level and got the same result (i.e. "ConfigPathSeq" string variable, EvaluateAsExpression=true @[User::ConfigPathMaster], scoped to the Sequence container level and set the Exec task variable to evaluate to this.)

I've got to be overlooking something really basic ;o(

Dan
more ▼

answered Oct 28, 2011 at 10:33 AM

dimsandwich gravatar image

dimsandwich
1 1 1 2

Sorry Dan, it took a few days to get time to look at this.

When you designate your configuration file make sure the only thing you have being configured is the value of the 'ConfigPathMaster' package variable. You should see a string in the XML file that looks similar to the following:

C:theconfigDev5config.dtsConfig

To verify, you can reload the package to look at the value of both ConfigPathMaster and ConfigPathExec. They should both have the value that you configured in the XML file.

You might want to name the child variable something different than the parent's variable name. That can help to eliminate any confusion with variable names/scope. Also, when you execute your package, make sure you have the "Output" window shown. This window will show you informational messages about which configuration the package is attempting to execute from. You should see something similar to the following:

Information: 0x40016042 at MasterPackage: The package is attempting to configure from the parent variable "ConfigPathExec".
Nov 03, 2011 at 11:58 AM the_SQL
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x27
x12

asked: Oct 17, 2011 at 03:03 AM

Seen: 2419 times

Last Updated: Oct 17, 2011 at 03:03 AM