July 5, 2011 at 3:47 pm
Hi, I have to execute the SSIS package from stored procedure. Is there any simple way other than xp_cmdshell? Eventhough I got the access, xp_cmdshell is not working for me. Please let me know if we can write a simple stored proceduer to run the SSIS package. Thanks.
July 5, 2011 at 4:11 pm
radb4u (7/5/2011)
Hi, I have to execute the SSIS package from stored procedure.
Why?
Is there any simple way other than xp_cmdshell?
You could put the call to your SSIS package in a SQL Agent job and then use sp_start_job to kick off the job.
Eventhough I got the access, xp_cmdshell is not working for me.
What does "not working for me" mean? Is there an error message you're receiving?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 6, 2011 at 7:50 am
Thank you for the reply. Other peopl would like run my SSIS, simply by calling it. When I run the xp_cmdshell, I am getting 'The xp_cmdshell proxy account information cannot be retrieved or is invalid'. I will look into sql agent job. Thank you.
July 6, 2011 at 9:35 am
You're receiving the error because you are not in the sysadmin Server Role. Someone may have granted you exec permissions on xp_CmdShell however they did not setup the xp_CmdShell proxy Credential.
SQL Agent is an option however just know that sp_start_job starts a job asynchronously. If you need to wait for the job to finish to return control to the caller you'll have to setup a polling process to wait for the job to complete. There are tons of examples of that technique online for you, tons on this site alone for that matter.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply