?
Solved

Run SSIS package from xp_cmdshell error

Posted on 2009-05-12
10
Medium Priority
?
1,344 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
How to Create Failover DNS Record Sets in Route 53

Route 53 has the ability to easily configure DNS record sets specifically for failover scenarios. These failover record sets can be configured to failover to full-blown deployments in other regions or to a static HTML page that informs your customers of the issue.

 

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

Cloud Training Guides

FREE GUIDES: In-depth and hand-crafted Linux, AWS, OpenStack, DevOps, Azure, and Cloud training guides created by Linux Academy instructors and the community.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

764 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