Using xp_cmdshell to execute SSIS in a stored procedure

This is the code:

 

DECLARE @SQLCommand AS VARCHAR(1000)

SET @SQLCommand = '("dtexec location" /F "package location and name" '

+ '/SET \package.Variables[User::your variable name].Value;'

+ CONVERT(VARCHAR(2), MONTH(@Date)) + '/' + CONVERT(VARCHAR(2), DAY(@Date)) + '/' + CONVERT(VARCHAR(4), YEAR(@Date))

+ ')'

EXEC xp_cmdshell @SQLCommand

 

Some notes:

  • I tried to pass in the date without formatting it, I get a "Option "1" is not valid" message.  I had to convert the date into a string to include in the SQL command, but it's not taking the format "Sep  1  2009  12:00AM"
  • Make sure there is no extra space behind the ".Value;"  That tripped me up for half an hour.

 

 

[Update 2009-10-15]

Can't seem to set the variable if it's a BOOLEAN type. Had to convert the variable type into a VARCHAR and convert it. Not the way I wanted to do it, but everyone seems to agree that it's a SSIS 2005 bug.

 

 

[Update 2009-11-05]

If I have a chance to do this over again, I would not use xp_cmdshell. I would build a table to submit all my parameters, put my SSIS package as a SQL job, and have my stored procedure fire off that job. If I need to, I can stored the package status in the same table to use AJAX to report it.

There is too much security and permission risk with xp_cmdshell.

 

 

[Update on 2009-12-01]

This looks like a promising way downgrade the permission level of the calling user.  Going to try it:

http://www.kodyaz.com/blogs/software_development_blog/archive/2006/11/23/478.aspx

Print | posted on Wednesday, September 23, 2009 12:41 PM

Feedback

No comments posted yet.

Your comment:





 

Copyright © Kevin Shyr

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski