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