execute ssis package - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

execute ssis package

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-12-12 : 08:09:32

Hi,
Trying to pass parameters to one ssis package and execute it as below.
Note that I am using one exec per parameter for the same ssis package.
The error is:
'DTSExec' is not recognized as an internal or external command,
operable program or batch file.
NULL

EXEC xp_cmdshell 'DTSExec /SQL "\PackageName" /SERVER "(ServerName)" /SET "\Package.Variables[User::ServerName].Value";"\\servername"'
EXEC xp_cmdshell 'DTSExec /SQL "\PackageName" /SERVER "(MainPath)" /SET "\Package.Variables[User::MainPath].Value";"\d$\foldername"'
EXEC xp_cmdshell 'DTSExec /SQL "\PackageName" /SERVER "(MainPath)" /SET "\Package.Variables[User::FileName].Value";"filename"'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-12 : 10:19:47

its DTExec and not DTSExec

EXEC xp_cmdshell 'DTExec /SQL "\PackageName" /SERVER "(ServerName)" /SET "\Package.Variables[User::ServerName].Value";"\\servername"'
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-12-12 : 10:37:58

This is what I am doing now as I have to pass three parameters to one ssis package but there is an error:

EXEC xp_cmdshell
'DTExec /SQL "\packagename.dtsx" /SERVER "(servername)"
/SET "\Package.Variables[User::ServerName].Value";"\\servername"
/SET "\Package.Variables[User::MainPath].Value";"\d$\foldername"
/SET "\Package.Variables[User::FileName].Value";"filename"'
ERROR is:
Microsoft (R) SQL Server Execute Package Utility
Version 10.50.1600.1 for 64-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.
NULL
Started: 14:52:07
Could not load package "\packagename.dtsx" because of error 0xC001000A.
Description: The specified package could not be loaded from the SQL Server database.
Source:
Started: 14:52:07
Finished: 14:52:07
Elapsed: 0.047 seconds
NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-12 : 10:42:58

do you've package stored in MSDB folder of integration services engine in your server?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-12-12 : 10:49:57

No, they are under then stored packages\File Systems

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-12 : 10:54:20

then you should be using /F option and not /SQL option inside DTexec

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-12-12 : 11:03:33

Do you mean like:
EXEC xp_cmdshell
'DTExec /f "\packagename.dtsx" /SERVER "(servername)"
/SET "\Package.Variables[User::ServerName].Value";"\\servername"
/SET "\Package.Variables[User::MainPath].Value";"\d$\foldername"
/SET "\Package.Variables[User::FileName].Value";"filename"'

Should I put the whole path or is the above ok?
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-12 : 11:07:08

you need to put the whole path

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-12-12 : 11:13:19

If I do NOT pass the parameters as below then it works:
EXEC xp_cmdshell
'DTExec /f "D:\Users\ssisfolder\packagename.dtsx"'

BUT if I pass the parameters which is what I want to do then it fails:
EXEC xp_cmdshell
'DTExec /f "D:\Users\ssisfolder\packagename.dtsx" /SERVER "(servername)"
/SET "\Package.Variables[User::ServerName].Value";"\\servername"
/SET "\Package.Variables[User::MainPath].Value";"\d$\foldername"
/SET "\Package.Variables[User::FileName].Value";"filename"'

Am I doing something wrong when passing parameters?
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-12 : 11:16:35

you dont need /SERVER while storing package in file system. /SERVER is required only for /SQL or /DTS options

suggest you read and understand DTExec utility first

http://msdn.microsoft.com/en-us/library/ms162810.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-12-12 : 11:36:43

Thanks for the information...
This now works:
EXEC xp_cmdshell
'DTExec /f "D:\Users\packagefolder\packagename.dtsx"
/SET "\Package.Variables[User::ServerName].Value";"\\servername"
/SET "\Package.Variables[User::MainPath].Value";"\d$\foldername"
/SET "\Package.Variables[User::FileName].Value";"filename"'

I am unable to see why the following doe snot execute the package. Do you see why please?

EXEC xp_cmdshell
'DTExec /f "D:\Users\packagefolder\packagename.dtsx"
/SET "\Package.Variables[User::ServerName].Value";"\\servername"
/SET "\Package.Variables[User::MainPath].Value";"\d$\ssis"
/SET "\Package.Variables[User::FileName].Value";"filename"'
Looking closely the error message is:
Description: The For Each File enumerator is empty. The For Each File enumerator did not find any files that matched the file pattern, or the specified directory was empty.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-12 : 11:38:24

thats descriptive enough. its a warning telling that it didnt find any files matching type you specified inside the source folder

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-12-12 : 11:55:43

Yes, I understand that. This is what I am trying to figure out why it did not see the file.
The file is in the new path and it seems the MainPath is not being set and therefore not passed to the ssis package.
In the package variable for the mainpath, there is a default value which is \d$\foldername
My code with the /set ... should assign a new value which is \d$\ssis\ but it does not.
Do you see why pls?
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 00:31:58

you need to configure for each loop to use value from a variable then. This can be done by setting an expression for directory property inside for each loop.
Once its done, you can set the value for the variable using /SET in DTExec

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-12-13 : 04:26:20

Could it also be to do with teh fact that I am also using a package configuration which has the MainPath variable in it?
I havetried it in a different package which does not have a for loop.
The error is:
Description: Cannot open the datafile "\\servername\d$\foldername\filename.csv".

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 04:36:50

this error is because either file is not available or you cannot access it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-12-13 : 04:46:49

For sure, the file is there and can access it.
Definitely my SET statement does not work because:
1- I removed the value in the MainPath variable of the package and renamed the pckageconfiguration and disabled the package configuration from the package.

Running the exec code, the error is:
Description: Cannot open the datafile "\\servername\file name.csv".
NOTE, that there is no path in there whereas I entered it in my exec set statement.
So, the SET statement does not path parameter.
Do you see why please?
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 04:55:18

are you passing like this?

/SET "\Package.Variables[User::MainPath].Value";"\\servername\file name.csv"

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-12-13 : 05:02:57

This is what I have. I do not see anything wrong with it.
EXEC xp_cmdshell
'DTExec /f "D:\Users\packagefolder\packagename.dtsx"
/SET "\Package.Variables[User::ServerName].Value";"\\servername"
/SET "\Package.Variables[User::MainPath].Value";"\d$\foldername\ssis"
/SET "\Package.Variables[User::FileName].Value";"file name"'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 05:24:13

which variable holds the folder information for loop?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-12-13 : 05:50:13

I found the problem.
All the parameters have to be on one line instead of the way I showed you.
Thanks for your time.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources