Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1367
  • Last Modified:

Run SSIS package from xp_cmdshell error

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
lali_murray
Asked:
lali_murray
  • 6
  • 4
1 Solution
 
PedroCGDCommented:
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
 
lali_murrayAuthor Commented:
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
 
PedroCGDCommented:
the problem seems related with permissions, because you can run it from job and cannot from inside a SP.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
lali_murrayAuthor Commented:
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
 
PedroCGDCommented:
Lali,
Do you have the stored procedure xp_cmdsheel enabled in "Surface Area configuration for Features"?
0
 
lali_murrayAuthor Commented:
Yes, it is enabled. I've restarted the Agent to make sure everything is clean.
0
 
lali_murrayAuthor Commented:
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
 
PedroCGDCommented:
0
 
lali_murrayAuthor Commented:
Thanks a lot for you're help!!
0
 
lali_murrayAuthor Commented:
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

Industry Leaders: 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!

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now