Hello!
Please check following link:
This is an example of running SSIS package in the 32-bits mode from SQL stored procedure:
DECLARE @result INT,
@dtsExecCmd varchar(8000)
-- Assign values to package variables
-- @pNumber - SP parameter
SET @dtsExecCmd = ' /SET "\package.Variables[User::pNumber].Properties[Value]";' + cast(@pNumber as varchar(10))
SET @dtsExecCmd = 'c: & cd\ & cd "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\" & dtexec.exe /FILE "C:\SSIS\V5.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI ' + @dtsExecCmd
-- following lines will work when package was deployed on SQL and OS level by running of .SSISDeploymentManifest file.
-- SET @dtsExecCmd = 'c: & cd\ & cd "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\" & dtexec.exe /SQL "\V5" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW ' + @dtsExecCmd
-- SET @dtsExecCmd = 'c: & cd\ & cd "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\" & dtexec.exe /DTS "\MSDB\V5" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V ' + @dtsExecCmd
/*
Previuos lines let us to run SSIS package in the 32-bit mode, because we are running 32-bits version of dtexec.exe.
Note – cmdshell will run multiple DOS commands by one call (operands splitted by “&”).
*/
DECLARE @output TABLE( [ssisOutput] varchar(max) )
INSERT INTO @output
EXEC @result = master..xp_cmdshell @dtsExecCmd
if @pDebugMode = 1 -- input parameter of SQL sp
begin
SELECT * FROM @output
print @result
end
Good luck,
Sergey