I have been working on creating SSIS packages using Sql Server Data Tools in Visual Studio 2012.
I was running into a problem where I could run a package in Visual Studio, but it would not run when deployed to Integration Services on the same machine. What's the difference?
My package is using an ODBC connection using a Data Source setup on my machine using a 32-bit driver.
Behind the scenes SSIS runs DTExec.exe to execute the packages. The trick is that on 62-bit installations there are two different locations for DTExec.exe; one for 32-bit version and another for 62-bit version.
So to summarize... I've got an SSIS package that uses a 32-bit ODBC data source. When I run in Visual Studio all is fine because Visual Studio uses 32-bit. When I run in SSIS, I get errors because SSIS is calling a 64-bit version of DTExec which is then looking for my ODBC connection in the 64-bit Data Sources setup on the machine, which does not exist.
SO... I need to tell Sql Server SSIS to use the 32-bit DTExec even when running under 64-bit.
Don't worry, I am confusing myself at this point! But let's just get to the solution...
The fix for me was to edit the Registry settings for Sql Server and point the DTS path to the location of the 32-bit version of DTExec.
The registry setting for Sql Server 2012 is located in...
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\110\SSIS\Setup\DTSPath
Note: Edit registry at your own risk!
The default value is...
C:\Program Files\Microsoft SQL Server\110\DTS\
I changed it to...
C:\Program Files (x86)\Microsoft SQL Server\110\DTS\
There can be platform conflicts between data sources and the environment they eventually are called in. This little trick helped me get my SSIS packages running again. I hope it helps you, too.