64 bit SSIS running as 32 bit issues

Question

64 bit SSIS running as 32 bit issues

JMartin-392745

Ten Centuries

Points: 1279
More actions
December 10, 2008 at 9:27 am

#128352

Hello
I have written an SSIS package that makes use of 32 bit drivers for connecting to the data source, I can get this working by setting the relevent option in visual studio and it is fine. However I am struggling to get it to function under SQL 2005 SP2 64 bit.
I know that under SQL 2008 there is a /X86 option on DTExec, however does anyone know of a comparable option for SQL 2005?? I have had a look around and not been able to find anthing that makes much sense.
Cheers

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

That option is not available in the job agent in SQL 2005. You have to call the SSIS package using the CMD step type and give the path to the 32 bit DTExec.exe.

Search google a bit for this, it is pretty common.

JMartin-392745 Ten Centuries Points: 1279 More actions · Answer 2

JMartin-392745

Ten Centuries

Points: 1279

December 10, 2008 at 1:02 pm

#910626

ok I'll give it another go.

smorozov SSC Enthusiast Points: 182 More actions · Answer 3

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

kramaswamy SSCoach Points: 18181 More actions · Answer 4

Hey - I know this is a very old post, but I must have spent *hours* searching for the solution, and this was *exactly* what I needed.

Thanks a ton Sergey! You've saved me countless more hours of fruitless searching and forced re-writing of scripts!