If you’ve ever built an SSIS package that utilizes the Microsoft Jet Driver for an Access or Excel data source or a package that executes a SQL 2000 DTS package, you know that you must set the Run64bitRunTime property in the SSIS project properties to False.
This stinks if you’re executing these kinds of child packages from a master package that also executes child packages are not required to execute in 32 bit mode. Dry your eyes, my friend, because there is a work around for this.
You can download the Microsoft Access Database Engine 2010 component, which will allow you to connect to an Excel or Access source while running the package in 64bit mode (although I’ve never been able to get this component to actually work so if you have please leave a comment or send me an email). But if you are wanting to execute a DTS legacy package from a 64bit SSIS master package, you’ll just need to use an Execute Process Tasks to execute Dtexec 32 bit.
Even if you’re running on a 64 bit machine, the 32 bit version of Dtexec is installed by default. So in your Execute Process Task in the Process window, you’ll need to set the Executable property to your copy of Dtexec 32 bit, which is installed by default in <your drive>:\Program Files(x86)\Microsoft SQL Server\100\DTS\Binn (64 bit Dtexec is here: <your drive>:\Program Files\Microsoft SQL Server\100\DTS\Binn. And in the Arguments property, just enter in your command to execute the package that needs to run in 32 bit mode.
Executing your 32 bit child packages this way will allow the rest of your 64 bit runtime packages to continue to execute in 64 bit mode. If you’d like to continue to learn more about using Dtexec to run your packages, check out this very hand link from Microsoft.