I am going to have a stored procedure that will call and execute an
SSIS package. I was going to use the DTexec utilility but have not
found out how to call that utility from a stored procedure. Is the the
right approach or is there a better way? Is there an alternate way to
call an SSIS package from a stored procedure? any help is appreciated.
Thanks.

Dave Christman

Hello hedgracer,

The xp_cmdshell extended stored procedure allows you to command line programs

The only other alternative is to create a job, and then call sp_start_job.

Darren - I have setup a sp to execute dtexec using xp_cmdshell and this is
working great as long as the user is in the sysadmin db role. I understand
that if a non-sysadmin user executes my sp, SQL Server 2005 will attempt to
use the cmd shell proxy account - if it doesn't exist the exec will fail. I
have created a local Windows account and used this account for the cmd shell
proxy credential. When the non-sysadmin user attempts to execute my sp, the
proc returns the following error. What am i missing?

Microsoft OLE DB Provider for SQL Server error '80040e09'

EXECUTE permission denied on object 'xp_cmdshell', database
'mssqlsystemresource', schema 'sys'.


"Darren Green" wrote:

Hello SSM,

The error message looks pretty clear to me, how about granting permissions
on xp_cmdshell to your user (the one making the connection to SQL Server
to call xp_cmshell), have you done that?

This is a bad idea though from a security perspective though, please be aware
of that.

Darren

Thank you Darren - I appreciate the response. Yes, I thought the error msg
was pretty clear too and did grant execute permissions to xp_cmdshell for the
application login. This did not work either. And like you, I am concerned
with the security implications of doing so. Let me take this from a
different approach. Is there a means to use the SSIS/DTS object model, .NET,
and CLR integration to execute a SSIS package from a stored procedure?

"Darren Green" wrote:

I resolved my issue with xp_cmdshell as follows....

I had already tried granting the user execution permissions to the sp
without success (I did not want to create this security hole either)- I
started getting a different error message. One related to the object being
closed. I thought this error message was related to cmdshell. After some
research, I found this error message was related to an empty recordset being
returned to ASP. After some further research, I found that the command line
statement was reporting that it could not open the SSIS package. This was
hidden by the no_output clause in the cmdshell execution.

After working through this, I found that I did setup the Windows account and
cmdshell proxy correctly. I also had the Windows account setup as a database
login with sysadmin database role correctly. The additional steps I took
were adding the 'with execute as owner' clause to the stored procedure being
executed and giving the Windows account permissions to the file subdir where
I store the SSIS packages.


"SSM" wrote: