Solved

Run SSIS package from xp_cmdshell error

Posted on 2009-05-12
10
1,313 Views
Last Modified: 2013-11-10
Hi!
I have build a SSIS package that runs just fine when I start it with a DTEXEC command in a Job. It is usefull for me because I can then pass parameters to the package.

I want to make these parameters dynamic and use a SP, so I have created a @cmd tu use with xp_cmdshell. Unfortunately, my statement does not work this way, even if it's exaclty the same it was when I was just using DTEXEC in SQL Agent Job.
----------------- DTEXEC in JOB works just fine
dtexec /F "C:\Program Files\Microsoft SQL Server\90\DTS\Packages\Part Repository\Part Repository Dev\Part Repository Dev.dtsx" /DECRYPT Repository /SET \Package.Variables[User::CmdType].Properties[Value];"EXEC ONDT_GET_PART_REPOSITORY_INCREMENTAL" /SET \Package.Variables[User::TimeInterval].Properties[Value];"-5" /SET \Package.Variables[User::DB].Properties[Value];"1" /CONFIGFILE "C:\Program Files\Microsoft SQL Server\90\DTS\Packages\Part Repository\Part Repository Dev\PW_Part_Repository_Dev.dtsConfig"
 
------------------  DTEXEC with xp_cmdshell does not work
DECLARE @cmd varchar(1000)
DECLARE @FilePath varchar(200)
DECLARE @CmdType varchar(200) 
DECLARE @TimeInterval as int
DECLARE @Db as varchar
DECLARE @ConfigFile as varchar(200)
 
SET @FilePath = 'C:\Program Files\Microsoft SQL Server\90\DTS\Packages\Part Repository\Part Repository Dev\Part Repository Dev.dtsx'
SET @CmdType= 'EXEC ONDT_GET_PART_REPOSITORY_INCREMENTAL'
SET @TimeInterval  = -5
SET @Db = '1'
SET @ConfigFile = 'C:\Program Files\Microsoft SQL Server\90\DTS\Packages\Part Repository\Part Repository Dev\PW_Part_Repository_Dev.dtsConfig'
 
select @cmd = 'dtexec /F "' + @FilePath + '" /DECRYPT Repository'
select @cmd = @cmd + ' /SET \Package.Variables[User::CmdType].Properties[Value];"' + @CmdType + '"'
select @cmd = @cmd + ' /SET \Package.Variables[User::TimeInterval].Properties[Value];"' + convert(varchar,@TimeInterval) + '"' 
select @cmd = @cmd + ' /SET \Package.Variables[User::DB].Properties[Value];"' + @Db + '"'
select @cmd = @cmd + '/CONFIGFILE"' + @ConfigFile + '"'
 
Print @cmd
exec master..xp_cmdshell @cmd
 
Print shows: dtexec /F "C:\Program Files\Microsoft SQL Server\90\DTS\Packages\Part Repository\Part Repository Dev\Part Repository Dev.dtsx" /DECRYPT Repository /SET \Package.Variables[User::CmdType].Properties[Value];"EXEC ONDT_GET_PART_REPOSITORY_INCREMENTAL" /SET \Package.Variables[User::TimeInterval].Properties[Value];"-5" /SET \Package.Variables[User::DB].Properties[Value];"1"/CONFIGFILE"C:\Program Files\Microsoft SQL Server\90\DTS\Packages\Part Repository\Part Repository Dev\PW_Part_Repository_Dev.dtsConfig"

Open in new window

errorMessage.JPG
0
Comment
Question by:lali_murray
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 22

Expert Comment

by:PedroCGD
ID: 24365945
Use SQL Profiler to check the user that is running in both ways.
Also verify if you have SQL SP3 installed.
Give feedback
Regards,
Pedro
www.pedrocgd.blogspot.com
0
 

Author Comment

by:lali_murray
ID: 24366094
The server is On SP2. Unfortunately, the installation of SP3 is not possible for now.

When I run the DTEXEC from the job, the user I find in profiler is 'sa'

I cannot find anything in profiler (I'm not a SQL Profiler Expert) when it is ran from SP and xp_cmdshell
0
 
LVL 22

Accepted Solution

by:
PedroCGD earned 500 total points
ID: 24366191
the problem seems related with permissions, because you can run it from job and cannot from inside a SP.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:lali_murray
ID: 24366265
Do you have an idea of what security issues I may run into? I explicitely gave acces to the folder containing the SSIS package to the account used to launch SQL Agent. Same error...
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 24366330
Lali,
Do you have the stored procedure xp_cmdsheel enabled in "Surface Area configuration for Features"?
0
 

Author Comment

by:lali_murray
ID: 24366385
Yes, it is enabled. I've restarted the Agent to make sure everything is clean.
0
 

Author Comment

by:lali_murray
ID: 24366403
Not sure if it's an issue, but I have other instances on this server. But I work on the Root one. Just tought I could mention this.
0
 
LVL 22

Expert Comment

by:PedroCGD
ID: 24366445
0
 

Author Closing Comment

by:lali_murray
ID: 31580588
Thanks a lot for you're help!!
0
 

Author Comment

by:lali_murray
ID: 24366478
Great article! Thanks...

I had an issue with my config file. I've rebuild/ exported the package and it runs fine now. Thanks a lot !!!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question