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
asked Oct 17, 2011 at 03:03 AM in Default
It sounds like you are wanting to implement indirect package configuration. Check out this link where this is discussed:- [link text]: http://sqlscape.wordpress.com/tag/direct-vs-indirect-configuration/
answered Oct 19, 2011 at 08:02 AM
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?
answered Oct 24, 2011 at 10:12 AM
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
answered Oct 26, 2011 at 11:56 AM
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.
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.
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