Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Run SSIS package from xp_cmdshell error

Posted on 2009-05-12
10
Medium Priority
?
1,357 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 2000 total points
ID: 24366191
the problem seems related with permissions, because you can run it from job and cannot from inside a SP.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

609 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