Parent Child Integration using Configuration File

Hi All,

I though i'd been going about setting up my SSIS package to run via a SQL job in the correct way. It would appear however that is not the case.

I have 4 SSIS packages, one of which is the parent package which calls the other three in sequence. I want to run this from a SQL job so that it calls the parent package and it deals with the others. There are connection managers in the package which use a SQL account to access the relevant databases. In addition i have encrypted the package.

I have set up a configuration file which holds the password package and the BillingSystem connection manager password. This file is re used by each package.

After deployment i have set up the SQL agent job to run the parent package. The job returns an error. Looking at the SSIS logging it appears that it completes the SQL task in the parent package and then fails when trying to launch each of the sub packages with an error stating that the SQL account used in the connection manager login failed. It looks as though the sub packages are not getting the password value from the configuration file and this is causing the failure. I though that each package would automatically pick up the config file as it has been setup to do. I have specified the configuration file in the SQL job but this appears to make no difference. Running the integrations seperately i have to add the configuration file in manually to the Run Package dialog box before they will work. This is not saved for the next run.

Can anyone help me get around this problem as i think i have the wrong idea as to how it is supposed to work.

Cheers,

Grant

Are your packages stored in the filesystem or in MSDB?|||

MSDB; Does that make a difference in this case?

Grant

|||Is the SQL server connection for each of the Execute Package task part of a configuration file?|||

Initially the config file contained the connection manager password. It now has the connection string but doesn't seem to be used for the packages called from the initial package. I had to manually alter the i config xml to include the password in the connection string.

Grant