SSIS 64 Bit Gotchas

I’m at a client engagement this week, which has been extremely fun in learning the more subtle issues isses with 64 bit with SSIS. The first gotcha I alread knew but need to make sure you have a heads-up on is that Visual Studio is only a 32 bit application, which means you will not see BIDS on a 64 bit box by default. You can see Managment Studio though.

 

I was doing performance testing of the DB2 driver and noticed that it was working great until I deployed to the SQL Server’s IA64 server. Once I deployed, and executed the package by using Management Studio from the Package Store, it took anywhere from 40%-100% slower. After much tormoil and some great help from some great people, we found out that when you execute a package from the Package Store in Management Studio on the box, it uses the 32 bit version of DTSExec.exe. The marshalling process of going back and forth between 64 bit and 32 bit caused a larger slowdown. Problem was fixed upon scheduling the package or copying the command out of DTSRunUI and executing DTSRun.exe from the command prompt, which uses the 64 bit version of the utilitiy. After that, everything worked great!

 

We’ve found loads more gotchas so more to come!