Solved

Run SSIS package from xp_cmdshell error

Posted on 2009-05-12
10
1,277 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
  • 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now