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

Registering a SP in master?

(SQL Server 2000, SP3a)
Hello all!
A while ago I seem to recall a post that talked about the possibility of registering a
stored procedure in the master database, such that it became a "global" procedure with
public scope without having to prefix the execution with "master". However, I can't seem
to figure out how to perform this registration process.
Any ideas?
John PetersonAny procedure in master, that is owned by dbo, and begins with sp_ will be
accessible from any database without specifying the db or owner name.
However, you still must grant permissions to those users who are allowed to
execute the procedure.
USE Master
CREATE PROC sp_myproc
AS ...
RETURN
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:#9qNHMdkDHA.1284@.TK2MSFTNGP09.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> A while ago I seem to recall a post that talked about the possibility of
registering a
> stored procedure in the master database, such that it became a "global"
procedure with
> public scope without having to prefix the execution with "master".
However, I can't seem
> to figure out how to perform this registration process.
> Any ideas?
> John Peterson
>|||Ah, gotcha! So kind of by virtue of residing in master and having the "sp_" prefix and
being owned by dbo, it'll be accessible to all other databases without having to
explicitly prefix the database or owner name. Thanks, Kalen! (And thanks for the
reminder about the permissions!)
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:u3G8bTdkDHA.2772@.TK2MSFTNGP12.phx.gbl...
> Any procedure in master, that is owned by dbo, and begins with sp_ will be
> accessible from any database without specifying the db or owner name.
> However, you still must grant permissions to those users who are allowed to
> execute the procedure.
> USE Master
> CREATE PROC sp_myproc
> AS ...
> RETURN
>
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:#9qNHMdkDHA.1284@.TK2MSFTNGP09.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > A while ago I seem to recall a post that talked about the possibility of
> registering a
> > stored procedure in the master database, such that it became a "global"
> procedure with
> > public scope without having to prefix the execution with "master".
> However, I can't seem
> > to figure out how to perform this registration process.
> >
> > Any ideas?
> >
> > John Peterson
> >
> >
>|||Yes, sp_ actually stands for 'special' because it gives your objects a
special behavior. :-)
Another note on permissions... permissions are granted to users, and most
database logins do not have specific user names in master. So your choices
really are between not granting permission to anyone, so only 'sa' can use
the procedure, and granting to public, so everyone can. Most of the system
procs (sp_help%, etc) have execute granted to public.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:ujiLi6dkDHA.2068@.TK2MSFTNGP09.phx.gbl...
> Ah, gotcha! So kind of by virtue of residing in master and having the
"sp_" prefix and
> being owned by dbo, it'll be accessible to all other databases without
having to
> explicitly prefix the database or owner name. Thanks, Kalen! (And thanks
for the
> reminder about the permissions!)
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:u3G8bTdkDHA.2772@.TK2MSFTNGP12.phx.gbl...
> > Any procedure in master, that is owned by dbo, and begins with sp_ will
be
> > accessible from any database without specifying the db or owner name.
> > However, you still must grant permissions to those users who are allowed
to
> > execute the procedure.
> >
> > USE Master
> > CREATE PROC sp_myproc
> > AS ...
> > RETURN
> >
> >
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:#9qNHMdkDHA.1284@.TK2MSFTNGP09.phx.gbl...
> > > (SQL Server 2000, SP3a)
> > >
> > > Hello all!
> > >
> > > A while ago I seem to recall a post that talked about the possibility
of
> > registering a
> > > stored procedure in the master database, such that it became a
"global"
> > procedure with
> > > public scope without having to prefix the execution with "master".
> > However, I can't seem
> > > to figure out how to perform this registration process.
> > >
> > > Any ideas?
> > >
> > > John Peterson
> > >
> > >
> >
> >
>