64-Bit Issues In SSIS

As mentioned before, DTExecUI.exe is a 32-bit application. Therefore, whenever you execute a package from DTExecUI.exe, it will execute in 32-bit mode and potentially take longer to execute than if you were executing it on your development machine. Much of the reason for this is data must be marshalled back and forth between 32-bit mode and 64-bit mode, and the amount of memory available differs. Also, Visual Studio is a 32-bit process, so you will need to run your packages out of the Visual Studio debug mode to get a true 64-bit run. To get around this problem, you can go to the Command Line page of this tool, copy the command out of the window, and paste it into a command prompt, prefixing dtexec.exe in front of it.

A particularly annoying quirk (at the time of publication) is the lack of an MDAC driver for the 64-bit architecture. The impact of this is that you can’t execute packages in 64-bit mode if they refer to anything that uses a Jet driver, in particular, Access and Excel. If you need to do this, you can execute the package using the 32-bit version of DTExec.exe. Another option in SQL Server Data Tools is to right-click the project, select Properties, and set the Run 64Bit Runtime to false in the Debugging page. This will set packages inside the project to run in 32-bit mode when debugging.

Aside from the few quirks of the 64-bit architecture and SSIS, the benefits are incredible. Keep in mind that SSIS is very memory intensive. If you’re able to scale up the memory on demand with a 64-bit architecture, you have a truly compelling reason to upgrade. Even though tools like DTExecUI are not 64- bit ready, scheduled packages will run under 64-bit mode. If you want a package to run under 32-bit mode, you have to add the step to run the 32-bit DTExec from the scheduled job by going to the runtime option in the Execution Options tab in SQL Server Agent.

Frequently Asked SSIS Interview Questions & Answers

List of Related Microsoft Certification Courses: