Relative path for child packages

hello again!, this time I'm trying to run a Master Package from the SQL Server Agent but I can't set relative paths to the connections for all the child packages that the master package contains.
It only finishes execution when I set absolute paths for all connections in the connection manager within the SSIS Project.

Is there any property in the SQL Server Agent or mayby a workaround to solve this?
Use absolute paths. Any reason you need relative paths?|||

Hi Santiago,

Unfortunately you cannot pass parameters to SQL Agent jobs. Workarounds usually involve:

1. Package configurations: set the path in a variable and use Expressions in the Package Connection Managers to dynamically apply the variable value.

2. Call the package dynamically: use a stored procedure to build a "dtexec" command-line and execute it via xp_cmdshell.

There are other ways to accomplish this as well. Personally, I recommend package configurations. I use xp_cmdshell less and less these days and when I do I enable it, do what I need to do, and disable it.

Hope this helps,

Andy

|||You can use a variable and expression-based connection strings if you need the paths to be dynamic at runtime. I usually store a root path in a variable, then use expressions to prefix that onto the name of the child package before calling it.|||

jwelch wrote:

You can use a variable and expression-based connection strings if you need the paths to be dynamic at runtime. I usually store a root path in a variable, then use expressions to prefix that onto the name of the child package before calling it.

Same here. This is (one of) the reason(s) that I always use the same folder structure on all of my projects.

Common folder structure

(http://blogs.conchango.com/jamiethomson/archive/2006/01/05/SSIS_3A00_-Common-folder-structure.aspx)

I also include the root path variable in my package template.

SSIS: Package Template

(http://blogs.conchango.com/jamiethomson/archive/2007/03/11/SSIS_3A00_-Package-Template.aspx)

-Jamie