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

1 1 1 2

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

6 answers: sort voted first

Hi, the_SQL, thanks for taking the time. I really appreciate it. Sadly, still no joy. I did the above and got the same result :o( The Master package is configured to read a dtsconfig file from C:\Configpath.dtsconfig. That sets the Package scoped ConfigPathMaster variable ok. I can also see that ConfigPathExec has evaluated ok. I gave the child package with an empty string variable scoped at Child package level called ConfigPathChild. The only configuration in the child package is a Parent package variable configuration that takes source from ConfigPathExec and sets ConfigPathChild.Value. Close and save both.
Re-open Master and see the info msg "The package is attempting to configure from the XML file "C:ConfigPath.dtsConfig"".

I right click on the Execute package task for the Child package. Child opens and there are no configuration messages issued and it fails to set ConfigPathChild to anything.

So then I though maybe you meant that every child should have a configuration that reads in C:\Configpath.dtsconfig and sets ConfigPathChild and then a 2nd config that is setup as a parent variable config still using ConfigPathChild as the target variable. So I tried that and while the ConfigPathChild variable gets set, It continues to be ignored with no informational messages.

I just can't see what the heck am I not doing to get the child to load the passed config?

What do you think?

(Desperate) Dan
more ▼

answered Nov 06, 2011 at 08:36 AM

dimsandwich gravatar image

1 1 1 2

(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



Answers and Comments

SQL Server Central

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



asked: Oct 17, 2011 at 03:03 AM

Seen: 2602 times

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