Subscribe to this blog
Briefcase
Print
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
- Use the 32-bit version of dtexec and execute your package from the command line which can be found in the Program Files (x86) folder.
- Follow link to execute package from command line http://msdn.microsoft.com/en-us/library/ms162810.aspx.
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