Slow Progress

I spent a frustrating morning with a client recently trying to use a Progress database as the source for an Integration Services package. The fix, as is so often the case, was simple, but I figured it was worthwhile documenting it here. I'm afraid that it's a more technical post than I usually put here, but if I can save someone else the same trouble we had then it'll be worthwhile.

The problem was as follows: we were trying to automate importing data from a Progress database to an MS SQL database using an SSIS package built on a 64-bit Windows 7 machine. We had an ODBC link set up (using the 32-bit OpenEdge drivers) that appeared to be working fine for the Import/Export Wizard and source previews and the likes - we could see and query data - but failed with ODBC error messages when run as an SSIS package from within the Business Intelligence Development Studio. "ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified" 

Google searching the problem wasn't very encouraging. We tried a dozen different workarounds, from editing the connection strings to bypass the ODBC DSN, registry hacks, trying to fake a 64-bit ODBC connection, but nothing helped. Then, as sometimes happens, I remembered a setting in BIDS that I'd seen a long time ago, never used and then forgot about.

In BIDS, select "Properties" from the "Project" menu. Select "Debugging" on the left-hand side of the Property Pages dialog, then change the value of Run64BitRuntime under "Debug Options" to "False". That simple. Once I'd done that, the import package ran like a train.

John Paul Cook has written an excellent article on the topic, including the vital 32-bit runtime setting needed when you deploy your working dev package to a 64-bit production Integration Services server.