Wednesday, 19 March 2014

1.19 - Running a 32bit SSIS Package in a 64bit SQL Environment with the SQL Agent

I have always seen lots of issues with this whenever I have been performing these tasks and I have now found a method that ensures that it will work. This is as follows:

  1. Change the SSIS package to run as 32bit
  2. Import the SSIS package as a file system deployment
  3. Create a credential on the SQL server
  4. Create a Proxy for the above credential for the cmd.exec
  5. Create a sql agent job to use a Operating System (Cmd Exec) in the job step
  6. Set the SQL agent to use the proxy account that was created above
  7. Now here's the part that forces it to use the 32bit  - Set the job step to call the 32bit version of the dtexec.exe (i.e. "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /f  "[package file location and name]")
I hope this helps others as this is was a godsend for me

No comments:

Post a Comment