master/Child Deployment

Hi Guru's,

I am facing a problem in deploying the SSIS packages.

My Scenario is like this

1. Have one Master SSIS Package.

2. Master SSIS Package inturn calls 3 Different packages using execute package task.

3. I have a common Configuration file for all these packages.

4. I am using File System deployment.

5. I have imported all the packages i.e master and the 3 child packages using SQL Server management studio.

6. Now i have created a job, and added the master package as a step.

7. I have a variable declared in the configuration file and the child packages will be getting their path from this variable.

The problem since i know the imported packages lies in "C:\Program Files\Microsoft SQL Server\90\DTS\Packages\<<Folder Name>>" i have poited the child package variable to point to this directory.

I still want to import all the packages and want to get this directory info dynamically thorugh the script so that i can assign this at run time.

I am using "Directory.GetCurrentDirectory" But it's giving me a different location when we run the master package.

Can any one has any idea or comments how to retrive the path of the master package?

Even a work around or any other option is also fine....

Regads,

Dev

I may not be clear on your requirements, but here's what I do:

I have a configuration file that is used by all packages, for database connections and whatnot, and a I have one that is specifically for the master. If any of the child packages need anything from the master configuration file, the master package will pass the information down to them.

The master has a variable named ChildPackagePath which is the folder location of the child packages. This variable is set by the master's configuration file. I then use expressions on the connection managers to the child packages to set the Connection String property as @.ChildPackagePath + "packagname.dtsx".

This allows you to vary the location of the child packages by the server environment. I don't think that determining the package location dynamically at runtime is possible. Your best bet is to use a path defined in a configuration file, or use a path that is dynamically available in an environment variable such as USERPROFILE.

master.dbo.xp_cmdshell is failing

Please help !
I run an ftp task which picks up a file from an ftp server through a package it runs fine - no errors.
If I schedule the same dts or if I run the following code in query analyser it fails
declare @.cmd varchar(2000)
declare @.ftpcommandfile varchar(2000)
--Get list of remote files
set @.ftpcommandfile = '-s:\\mathshr01\Marketing\Wr_mrktg_s\SHARED\WM_IM\Collections\Dialer\Dialer_Files\GetDialerFile.txt'
set @.cmd = 'ftp -i -s:' + @.ftpcommandfile
Exec master.dbo.xp_cmdshell @.cmd
I get the following message:
output ---
Error opening script file -s:\\mathshr01\Marketing\Wr_mrktg_s\SHARED\WM_IM\Collections\Dialer\Dialer_Files\GetDialerFile.txt.
Transfers files to and from a computer running an FTP server service
(sometimes called a daemon). Ftp can be used interactively.
FTP [-v] [-d] [-i] [-n] [-g] [-s:filename] [-a] [-A] [-x:sendbuffer] [-r:recvbuffer] [-b:asyncbuffers] [-w:windowsize] [host]
-v Suppresses display of remote server responses.
-n Suppresses auto-login upon initial connection.
-i Turns off interactive prompting during multiple file
transfers.
-d Enables debugging.
-g Disables filename globbing (see GLOB command).
-s:filename Specifies a text file containing FTP commands; the
commands will automatically run after FTP starts.
-a Use any local interface when binding data connection.
-A login as anonymous.
-x:send sockbuf Overrides the default SO_SNDBUF size of 8192.
-r:recv sockbuf Overrides the default SO_RCVBUF size of 8192.
-b:async count Overrides the default async count of 3
-w:buffer size Overrides the default transfer buffer size of 65535.
host Specifies the host name or IP address of the remote
host to connect to.
Notes:
- mget and mput commands take y/n/q for yes/no/quit.
- Use Control-C to abort commands.
NULLYes, the error is in the FTP command, the s parameter is included twice.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Aman" <Aman@.discussions.microsoft.com> wrote in message
news:641A9145-D95F-4D9B-A037-603546DA9178@.microsoft.com...
> Please help !
> I run an ftp task which picks up a file from an ftp server through a
package it runs fine - no errors.
> If I schedule the same dts or if I run the following code in query
analyser it fails
> declare @.cmd varchar(2000)
> declare @.ftpcommandfile varchar(2000)
> --Get list of remote files
> set @.ftpcommandfile ='-s:\\mathshr01\Marketing\Wr_mrktg_s\SHARED\WM_IM\Collections\Dialer\Dialer_
Files\GetDialerFile.txt'
> set @.cmd = 'ftp -i -s:' + @.ftpcommandfile
> Exec master.dbo.xp_cmdshell @.cmd
> I get the following message:
>
---
> Error opening script
file -s:\\mathshr01\Marketing\Wr_mrktg_s\SHARED\WM_IM\Collections\Dialer\Dia
ler_Files\GetDialerFile.txt.
> Transfers files to and from a computer running an FTP server service
> (sometimes called a daemon). Ftp can be used interactively.
> FTP [-v] [-d] [-i] [-n] [-g] [-s:filename] [-a] [-A] [-x:sendbuffer]
[-r:recvbuffer] [-b:asyncbuffers] [-w:windowsize] [host]
>
> -v Suppresses display of remote server responses.
> -n Suppresses auto-login upon initial connection.
> -i Turns off interactive prompting during multiple file
> transfers.
> -d Enables debugging.
> -g Disables filename globbing (see GLOB command).
> -s:filename Specifies a text file containing FTP commands; the
> commands will automatically run after FTP starts.
> -a Use any local interface when binding data connection.
> -A login as anonymous.
> -x:send sockbuf Overrides the default SO_SNDBUF size of 8192.
> -r:recv sockbuf Overrides the default SO_RCVBUF size of 8192.
> -b:async count Overrides the default async count of 3
> -w:buffer size Overrides the default transfer buffer size of 65535.
> host Specifies the host name or IP address of the remote
> host to connect to.
> Notes:
> - mget and mput commands take y/n/q for yes/no/quit.
> - Use Control-C to abort commands.
> NULL
>

master.dbo.xp_cmdshell is failing

Please help !
I run an ftp task which picks up a file from an ftp server through a package it runs fine - no errors.
If I schedule the same dts or if I run the following code in query analyser it fails
declare @.cmd varchar(2000)
declare @.ftpcommandfile varchar(2000)
--Get list of remote files
set @.ftpcommandfile = '-s:\\mathshr01\Marketing\Wr_mrktg_s\SHARED\WM_IM\Co llections\Dialer\Dialer_Files\GetDialerFile.txt'
set @.cmd = 'ftp -i -s:' + @.ftpcommandfile
Exec master.dbo.xp_cmdshell @.cmd
I get the following message:
output ---
Error opening script file -s:\\mathshr01\Marketing\Wr_mrktg_s\SHARED\WM_IM\Co llections\Dialer\Dialer_Files\GetDialerFile.txt.
Transfers files to and from a computer running an FTP server service
(sometimes called a daemon). Ftp can be used interactively.
FTP [-v] [-d] [-i] [-n] [-g] [-s:filename] [-a] [-A] [-x:sendbuffer] [-r:recvbuffer] [-b:asyncbuffers] [-w:windowsize] [host]
-v Suppresses display of remote server responses.
-n Suppresses auto-login upon initial connection.
-i Turns off interactive prompting during multiple file
transfers.
-d Enables debugging.
-g Disables filename globbing (see GLOB command).
-s:filename Specifies a text file containing FTP commands; the
commands will automatically run after FTP starts.
-a Use any local interface when binding data connection.
-A login as anonymous.
-x:send sockbuf Overrides the default SO_SNDBUF size of 8192.
-r:recv sockbuf Overrides the default SO_RCVBUF size of 8192.
-b:async count Overrides the default async count of 3
-w:buffer size Overrides the default transfer buffer size of 65535.
host Specifies the host name or IP address of the remote
host to connect to.
Notes:
- mget and mput commands take y/n/q for yes/no/quit.
- Use Control-C to abort commands.
NULL
Add PRINT @.cmd before the xp_cmdshell to see what the caommand is, but I think you have -s: in there twice.
"Aman" wrote:

> Please help !
> I run an ftp task which picks up a file from an ftp server through a package it runs fine - no errors.
> If I schedule the same dts or if I run the following code in query analyser it fails
> declare @.cmd varchar(2000)
> declare @.ftpcommandfile varchar(2000)
> --Get list of remote files
> set @.ftpcommandfile = '-s:\\mathshr01\Marketing\Wr_mrktg_s\SHARED\WM_IM\Co llections\Dialer\Dialer_Files\GetDialerFile.txt'
> set @.cmd = 'ftp -i -s:' + @.ftpcommandfile
> Exec master.dbo.xp_cmdshell @.cmd
> I get the following message:
> output ---
> Error opening script file -s:\\mathshr01\Marketing\Wr_mrktg_s\SHARED\WM_IM\Co llections\Dialer\Dialer_Files\GetDialerFile.txt.
> Transfers files to and from a computer running an FTP server service
> (sometimes called a daemon). Ftp can be used interactively.
> FTP [-v] [-d] [-i] [-n] [-g] [-s:filename] [-a] [-A] [-x:sendbuffer] [-r:recvbuffer] [-b:asyncbuffers] [-w:windowsize] [host]
>
> -v Suppresses display of remote server responses.
> -n Suppresses auto-login upon initial connection.
> -i Turns off interactive prompting during multiple file
> transfers.
> -d Enables debugging.
> -g Disables filename globbing (see GLOB command).
> -s:filename Specifies a text file containing FTP commands; the
> commands will automatically run after FTP starts.
> -a Use any local interface when binding data connection.
> -A login as anonymous.
> -x:send sockbuf Overrides the default SO_SNDBUF size of 8192.
> -r:recv sockbuf Overrides the default SO_RCVBUF size of 8192.
> -b:async count Overrides the default async count of 3
> -w:buffer size Overrides the default transfer buffer size of 65535.
> host Specifies the host name or IP address of the remote
> host to connect to.
> Notes:
> - mget and mput commands take y/n/q for yes/no/quit.
> - Use Control-C to abort commands.
> NULL
>
|||Yes, the error is in the FTP command, the s parameter is included twice.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Aman" <Aman@.discussions.microsoft.com> wrote in message
news:641A9145-D95F-4D9B-A037-603546DA9178@.microsoft.com...
> Please help !
> I run an ftp task which picks up a file from an ftp server through a
package it runs fine - no errors.
> If I schedule the same dts or if I run the following code in query
analyser it fails
> declare @.cmd varchar(2000)
> declare @.ftpcommandfile varchar(2000)
> --Get list of remote files
> set @.ftpcommandfile =
'-s:\\mathshr01\Marketing\Wr_mrktg_s\SHARED\WM_IM\Co llections\Dialer\Dialer_
Files\GetDialerFile.txt'
> set @.cmd = 'ftp -i -s:' + @.ftpcommandfile
> Exec master.dbo.xp_cmdshell @.cmd
> I get the following message:
>

> Error opening script
file -s:\\mathshr01\Marketing\Wr_mrktg_s\SHARED\WM_IM\Co llections\Dialer\Dia
ler_Files\GetDialerFile.txt.
> Transfers files to and from a computer running an FTP server service
> (sometimes called a daemon). Ftp can be used interactively.
> FTP [-v] [-d] [-i] [-n] [-g] [-s:filename] [-a] [-A] [-x:sendbuffer]
[-r:recvbuffer] [-b:asyncbuffers] [-w:windowsize] [host]
>
> -v Suppresses display of remote server responses.
> -n Suppresses auto-login upon initial connection.
> -i Turns off interactive prompting during multiple file
> transfers.
> -d Enables debugging.
> -g Disables filename globbing (see GLOB command).
> -s:filename Specifies a text file containing FTP commands; the
> commands will automatically run after FTP starts.
> -a Use any local interface when binding data connection.
> -A login as anonymous.
> -x:send sockbuf Overrides the default SO_SNDBUF size of 8192.
> -r:recv sockbuf Overrides the default SO_RCVBUF size of 8192.
> -b:async count Overrides the default async count of 3
> -w:buffer size Overrides the default transfer buffer size of 65535.
> host Specifies the host name or IP address of the remote
> host to connect to.
> Notes:
> - mget and mput commands take y/n/q for yes/no/quit.
> - Use Control-C to abort commands.
> NULL
>

master.dbo.xp_cmdshell is failing

Please help !
I run an ftp task which picks up a file from an ftp server through a package
it runs fine - no errors.
If I schedule the same dts or if I run the following code in query analyser
it fails
declare @.cmd varchar(2000)
declare @.ftpcommandfile varchar(2000)
--Get list of remote files
set @.ftpcommandfile = '- s:\\mathshr01\Marketing\Wr_mrktg_s\SHARE
D\WM_IM\Col
lections\Dialer\Dialer_Files\GetDialerFi
le.txt'
set @.cmd = 'ftp -i -s:' + @.ftpcommandfile
Exec master.dbo.xp_cmdshell @.cmd
I get the following message:
output
--
--
Error opening script file - s:\\mathshr01\Marketing\Wr_mrktg_s\SHARE
D\WM_IM\C
ollections\Dialer\Dialer_Files\GetDialer
File.txt.
Transfers files to and from a computer running an FTP server service
(sometimes called a daemon). Ftp can be used interactively.
FTP [-v] [-d] [-i] [-n] [-g] [-s:filename] [-a]
[-A] [-x:sendbuffer] [-r:recvbuffer] [-b:asyncbuffers] [
-w:windowsize] [host]
-v Suppresses display of remote server responses.
-n Suppresses auto-login upon initial connection.
-i Turns off interactive prompting during multiple file
transfers.
-d Enables debugging.
-g Disables filename globbing (see GLOB command).
-s:filename Specifies a text file containing FTP commands; the
commands will automatically run after FTP starts.
-a Use any local interface when binding data connection.
-A login as anonymous.
-x:send sockbuf Overrides the default SO_SNDBUF size of 8192.
-r:recv sockbuf Overrides the default SO_RCVBUF size of 8192.
-b:async count Overrides the default async count of 3
-w:buffer size Overrides the default transfer buffer size of 65535.
host Specifies the host name or IP address of the remote
host to connect to.
Notes:
- mget and mput commands take y/n/q for yes/no/quit.
- Use Control-C to abort commands.
NULLAdd PRINT @.cmd before the xp_cmdshell to see what the caommand is, but I th
ink you have -s: in there twice.
"Aman" wrote:

> Please help !
> I run an ftp task which picks up a file from an ftp server through a packa
ge it runs fine - no errors.
> If I schedule the same dts or if I run the following code in query analys
er it fails
> declare @.cmd varchar(2000)
> declare @.ftpcommandfile varchar(2000)
> --Get list of remote files
> set @.ftpcommandfile = '- s:\\mathshr01\Marketing\Wr_mrktg_s\SHARE
D\WM_IM\
Collections\Dialer\Dialer_Files\GetDiale
rFile.txt'
> set @.cmd = 'ftp -i -s:' + @.ftpcommandfile
> Exec master.dbo.xp_cmdshell @.cmd
> I get the following message:
> output
--
--
> Error opening script file - s:\\mathshr01\Marketing\Wr_mrktg_s\SHARE
D\WM_IM
\Collections\Dialer\Dialer_Files\GetDial
erFile.txt.
> Transfers files to and from a computer running an FTP server service
> (sometimes called a daemon). Ftp can be used interactively.
> FTP [-v] [-d] [-i] [-n] [-g] [-s:filename] [-a
] [-A] [-x:sendbuffer] [-r:recvbuffer] [-b:asyncbuffers]
1;-w:windowsize] [host]
>
> -v Suppresses display of remote server responses.
> -n Suppresses auto-login upon initial connection.
> -i Turns off interactive prompting during multiple file
> transfers.
> -d Enables debugging.
> -g Disables filename globbing (see GLOB command).
> -s:filename Specifies a text file containing FTP commands; the
> commands will automatically run after FTP starts.
> -a Use any local interface when binding data connection.
> -A login as anonymous.
> -x:send sockbuf Overrides the default SO_SNDBUF size of 8192.
> -r:recv sockbuf Overrides the default SO_RCVBUF size of 8192.
> -b:async count Overrides the default async count of 3
> -w:buffer size Overrides the default transfer buffer size of 65535.
> host Specifies the host name or IP address of the remote
> host to connect to.
> Notes:
> - mget and mput commands take y/n/q for yes/no/quit.
> - Use Control-C to abort commands.
> NULL
>|||Yes, the error is in the FTP command, the s parameter is included twice.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Aman" <Aman@.discussions.microsoft.com> wrote in message
news:641A9145-D95F-4D9B-A037-603546DA9178@.microsoft.com...
> Please help !
> I run an ftp task which picks up a file from an ftp server through a
package it runs fine - no errors.
> If I schedule the same dts or if I run the following code in query
analyser it fails
> declare @.cmd varchar(2000)
> declare @.ftpcommandfile varchar(2000)
> --Get list of remote files
> set @.ftpcommandfile =
'- s:\\mathshr01\Marketing\Wr_mrktg_s\SHARE
D\WM_IM\Collections\Dialer\Dialer_
Files\GetDialerFile.txt'
> set @.cmd = 'ftp -i -s:' + @.ftpcommandfile
> Exec master.dbo.xp_cmdshell @.cmd
> I get the following message:
>
---
> Error opening script
file - s:\\mathshr01\Marketing\Wr_mrktg_s\SHARE
D\WM_IM\Collections\Dialer\Dia
ler_Files\GetDialerFile.txt.
> Transfers files to and from a computer running an FTP server service
> (sometimes called a daemon). Ftp can be used interactively.
> FTP [-v] [-d] [-i] [-n] [-g] [-s:filename] [-a] [-
A] [-x:sendbuffer]
[-r:recvbuffer] [-b:asyncbuffers] [-w:windowsize] [host]
>
> -v Suppresses display of remote server responses.
> -n Suppresses auto-login upon initial connection.
> -i Turns off interactive prompting during multiple file
> transfers.
> -d Enables debugging.
> -g Disables filename globbing (see GLOB command).
> -s:filename Specifies a text file containing FTP commands; the
> commands will automatically run after FTP starts.
> -a Use any local interface when binding data connection.
> -A login as anonymous.
> -x:send sockbuf Overrides the default SO_SNDBUF size of 8192.
> -r:recv sockbuf Overrides the default SO_RCVBUF size of 8192.
> -b:async count Overrides the default async count of 3
> -w:buffer size Overrides the default transfer buffer size of 65535.
> host Specifies the host name or IP address of the remote
> host to connect to.
> Notes:
> - mget and mput commands take y/n/q for yes/no/quit.
> - Use Control-C to abort commands.
> NULL
>