How to run SSIS packages using 32-bit drivers on a 64-bit machine

Melissa Connors

Published On: November 12, 2019

Categories: SSIS 0

You might think it’s odd that I’m here to talk about DTS, but for many people out there regularly poring over our DTS xChange documentation, DTS is still a daily reality (no DeLorean, lightning, or plutonium required to work with it).

SQL Server DTS MemeIf you’re one of the people struggling to bring your organization’s SQL Server Data Transformation Services (DTS) packages to the current ETL known as SQL Server Integration Services (SSIS), then DTS xChange can help.

One of the more common questions people run into when getting started with DTS xChange is related to 32-bit drivers on a 64-bit machine, and I'll address that in this post.

How to run SSIS packages using 32-bit drivers on a 64-bit machine

 

Installing integration services on a 64-bit operating system automatically installs the 32-bit and 64-bit versions of DTExec commandline tool used to execute packages in SSIS.

DTExec Version File Path
DTExec 32-Bit C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn
DTExec 64-Bit C:\Program Files\Microsoft SQL Server\90\DTS\Binn

For more information about the DTExec installation, see dtexec Utility (SSIS Tool) MSDN article.

Note:  You must use the 32-Bit version of DTExec to execute SSIS package if your SSIS package references any 32-Bit DLL or 32-Bit drivers from your package. 

For example, if you're using a 32-bit driver like Microsoft Jet Driver (MS Access or Excel), there is no 64-Bit version of Jet Driver so any packages using Jet driver (Excel or MS Access Database Connections) must be called using 32 bit DTExec.

Note:  When you are designing such SSIS packages in Visual Studio and you have references to a 32-Bit driver/dll, make sure you change Project Property Run64BitRuntime to False before you debug your package in BIDS, otherwise your package will try to load 64-Bit dlls instead of 32-Bit. Change this setting by completing the following steps: 

  • Right click on Project Node.
  • Select Debugging, and change Run64BitRuntime to False.
  • SSIS 2016 Property Pages Screenshot

About DTS xChange

 

DTS xChange provides automated conversion of SQL Server 2000 DTS packages to SSIS 2005 and SSIS 2008. To learn more about its full features and how you can migrate to SSIS in minutes, see the DTS xChange product page.

Melissa (@MelikaNoKaOi) is the Product Education Manager at SentryOne. Melissa has over a decade of experience with SQL Server through software performance and scalability testing, analysis and research projects, application development, and technical support.


Comments