Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Devin Knight

Devin is a BI consultant at Pragmatic Works Consulting. Previously, he has tech edited the book Professional Microsoft SQL Server 2008 Integration Services and was an author in the book Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services. Devin has spoken at past conferences like PASS and at several SQL Saturday events. He is a contributing member to the Business Intelligence Special Interest Group (SIG) for PASS as a leader in the SSIS Focus Group. Making his home in Jacksonville, FL, Devin is a participating member of the local users’ group (JSSUG).

Running SSIS 32-bit drivers or tasks on a 64 bit machine

This is an answer to a forum question on www.bidn.com

Running SSIS on a 64-bit machine has several caveat that developers need to be aware of.  Tasks like the ActiveX task and Execute DTS 2000 Package Task only work either on a 32-bit machine or by running in 32-bit mode on a 64-bit machine.  This is also the case for many data providers like Excel, Access, and many ODBC drivers.  If you are working on a 64-bit machine don't worry there is a work around to keep yourself productive while still using 32-bit tasks and drivers.

Executing In BIDS

  • Right-click on the project file and select properties in your solution
  • Under the Debugging tab you will see a property called Run64BitRuntime which you need to change to true

 

Executing On Server

SQL Server 2008

  • Either in a SQL Agent job or by just executing the package by itself there is a tab called Execution options. 
  • Select Use 32 bit runtime and the package will then run in 32-bit mode

SQL Server 2005

Comments

Posted by smorozov on 14 January 2010

Running SSIS package under SQL server agent as a job does not let possibilites to manage package variables, but you can do that from stored procedure.

For example:

DECLARE @result INT,

@dtsExecCmd varchar(8000)

SET @dtsExecCmd = @dtsExecCmd + ' /SET "\package.Variables[User::pTable].Properties[Value]";' + @pTable

-- if package was deployed on the SQL

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

/*

-- it you just copied .dtsx file on the SQL server

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

*/

EXEC @result = master..xp_cmdshell @dtsExecCmd

I hope it is useful.

Thank you,

Sergey

Leave a Comment

Please register or log in to leave a comment.