• Hello!

    Please check following link:

    http://www.sqlservercentral.com/blogs/dknight/archive/2009/12/11/running-ssis-32-bit-drivers-or-tasks-on-a-64-bit-machine.aspx

    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