Help! My stored proc won't execute my SSIS package

Hi Experts,
I have created an SSIS package to import data into my SQL Server 2012 database.  I saved in the file system.  
I then created a stored procedure to execute my SSIS Package (see stored procedure code below).
When I run my stored procedure the data does not import.
When  I run the SSIS package using the Execute Package Utility, the job runs fine and all of my records get imported.

I need to be able to have my stored proc execute my SSIS package.  Are there any settings I need to change on the server?  Do I need to create a job for this?

ALTER PROCEDURE [dbo].[sp_executeSSISPackage] 
@returncode int

DECLARE @params nvarchar(400)

SET @params = 'dtexec /file "C:\TransferData\FileTrans.dtsx"'

EXEC @returncode = xp_cmdshell @params,no_output

select @returncode

Open in new window

Who is Participating?
mainrotorConnect With a Mentor Author Commented:
Okay, so I recreated my SSIS package using Windows Authentication instead of SQL Server Authentication and passing it a User Name and Password.  
Now the Stored Procedure that runs the SSIS package is working fine.  This is great as a temporary fix, but I need to be able to provide SQL Server authentication for this SSIS package.

Tony303, you asked "Is the account that is running the sp allowed to see file C:\TransferData\FileTrans.dtsx?"
and to be honest, I don't know.  I suppose not.  How can I give a particular account access to run the stored procedure and see a particular file?

Thanks in advance,

Is there an error message at all?

Is the account that is running the sp allowed to see file C:\TransferData\FileTrans.dtsx?

mainrotorAuthor Commented:
There is no error message.  Yes, the account running the SP allowed to run the Stored Procedure.
mainrotorAuthor Commented:
I selected my own response as the valid answere because I got my SSIS to work.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.