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]
DECLARE @params nvarchar(400)
SET @params = 'dtexec /file "C:\TransferData\FileTrans.dtsx"'
EXEC @returncode = xp_cmdshell @params,no_output