• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 513
  • Last Modified:

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

AS
BEGIN
DECLARE @params nvarchar(400)

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

EXEC @returncode = xp_cmdshell @params,no_output

select @returncode
END

Open in new window

0
mainrotor
Asked:
mainrotor
  • 3
1 Solution
 
Tony303Commented:
Hi,

Is there an error message at all?

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

Tony
0
 
mainrotorAuthor Commented:
There is no error message.  Yes, the account running the SP allowed to run the Stored Procedure.
0
 
mainrotorAuthor 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,
mrotor
0
 
mainrotorAuthor Commented:
I selected my own response as the valid answere because I got my SSIS to work.
0
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.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now