Wednesday, July 2, 2008

SSIS Child Package Location

I'm not sure if it's my stupidity or the SSIS designer interface but this one really threw me for a loop.

SSIS does not seem to like relative paths for child package locations (via the Execute Package task). In order to make this item configurable I created a few variables: one for the directory all the child packages will be located in, one for each child package to keep the package file name and one for each child package that concatenated two variables to get the complete package path. I then created a config file that contains just the child package directory value.

That's when things began to go haywire. I tried to set the expression for the Connection property of the Execute Package task to the variable containing the package path. Well, SSIS just did not understand me at all.

It turns out that I had falsely assumed that because the Connection property looked like it contained a file name that a file path would work in there. Wrong! That is the name of the Connection Manager! What I should have been doing was to set the ConnectionString property of the Connection Manager for the package. Doh!

While I accept responsibility for wasting a bunch of time figuring this out at least some of the blame falls on Microsoft for defaulting the new connection manager name to the file name of the package instead of allowing me to specify the name for the connection manager. From now on, I will explicitly rename the connection manager just to keep from confusing myself.