September 16, 2011 at 8:22 am
We are running SQL Server 2008. I created an SSIS package that runs on the file system of our network. I run this SSIS package using a simple batch file that contains the appropriate dtexec.exe command. I would like to write a stored procedure that will call the batch file (or run the SSIS package directly if that makes more sense). Any advise would be greatly appreciated.
September 16, 2011 at 9:56 am
How about creating a SQL Agent job with no default schedule, then run the job by calling your stored proc? the other option is using the xp_cmdshell system stored procedure to call your ssis package. Most peope do not enable xp_cmdshell though as its a potential security risk.
MCITP SQL 2005, MCSA SQL 2012
September 16, 2011 at 12:39 pm
Are you trying to give other unprivileged users the ability to kick off the package? If so setting appropriate permissions can be tricky. One potential solution is have the procedure set a flag in a table indicating the package should be run. Then have a frequestly scheduled job check the flag and then run the package if the flag is set.
September 19, 2011 at 8:28 am
Thanks for both responses to my inquiry, you have given me much to think about.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply