DTS

How to Fix Error Code: 80004005 in DTS Caused by 64-bit DB2 ODBC Driver

Aalam RangiLeave a comment

Issue:

Data Transformation Services packages (DTS) that use DB2 as an ODBC source, fail when the execution environment is upgraded to 64-Bit Windows Server 2008, Windows 7 etc.

Error Message:

The error logs of the DTS would show the following message –

Step Error Source: Microsoft OLE DB Provider for ODBC Drivers
Step Error Description:[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:0

Cause:

DTS are 32-bit applications and need the Data Source Names (DSN) based on 32-bit ODBC drivers. A 64-bit Operating System points to the 64-bit drivers by default and uses the ODBC Data Source Administrator at –

C:WINDOWSsystem32odbcad32.exe

The standard DSN creation scripts used by system administrators might use the 64-bit version because it is at the default location.

The 32-bit ODBC Data Source Administrator is in –

C:WINDOWSSysWOW64odbcad32.exe

The required DSN should be created again using this 32-bit version and it will update the Windows registry or the environment path variables appropriately.

Fix:

Start the 32-bit ODBC Data Source Administrator Wizard from the SYSWOW64 directory.

C:WINDOWSSysWOW64odbcad32.exe

Go to System DSN tab and click on Add.

clip_image001

Select the available IBM ODBC driver.

clip_image002

Give a DSN name.

clip_image003

Confirm the action.

clip_image004

The last message box verifies if you want to overwrite the existing DSN alias in DB2CLI.INI file. This file is at the below location on a 64-bit machine –

C:ProgramDataIBMDB2DB2_V95db2cli.ini

Clicking on No will abort the wizard. Click on Yes to continue. The timestamp for the INI file would be updated. If you are just recreating an existing alias as in the example above, the file content would appear to be the same, though the ODBC Data Source Administrator Wizard will create or modify the appropriate registry and environment variable entries.

Summary:

Microsoft maintains a backward compatibility feature known as WOW64 (Windows-32-On-Windows-64) that lets older 32-bit applications to run smoothly. This is implemented by maintaining a separate copy of the necessary 32-bit run time resources (configuration utilities, drivers, registry settings, other DLLs etc.) in different folders/locations.

This example refers to an ODBC DSN for a DB2 server but the concept could be relevant in other such scenarios where there is a conflict between 32 and 64-bit drivers.

Reference:

How to point to 32-bit IBM DB2 ODBC driver on 64-bit Microsoft system.
http://www-01.ibm.com/support/docview.wss?uid=swg21384435

DTS to SSIS Conversion Effort Estimate Workbook

Aalam Rangi6 Comments

I had to work on providing an effort estimate to convert multiple DTS packages into corresponding SSIS packages. An automated conversion tool was not available to us so the SSIS packages were to be built from scratch based on the existing DTS packages.

I took this opportunity to catalog all our DTS packages and created an Excel workbook to aid in the estimation. The formulas in the workbook calculate the effort in person-days for the rewrite of the current DTS logic into a SSIS package.

I approached this requirement as follows –

  1. Open each DTS package and used the Excel workbook to make an inventory of the type and number of all the tasks and connections in the package. Obviously, this is a repetitive and the most time consuming activity in the whole process.
  2. Decide on an estimated number of minutes required to recreate each type of task/connection in SSIS. This time could vary depending on the developer’s experience and skill.
  3. Lastly, put in standard time additions for activities like analysis, error handling, logging and documentation.

You can try that estimation workbook by downloading it at the link give below and tweaking the values to suit your needs. While you are there, don’t forget to rate it! 🙂 Please let me know in the comments section of this post if there are any suggestions or questions.

Download from the Technet Gallery link –
DTS to SSIS Conversion Effort Estimation Template.xlsx  approx size: 30 KB
http://gallery.technet.microsoft.com/DTS-to-SSIS-Conversion-7153a957

Do you have some of your own SSIS estimation tips? Please share in the comments section here or at this SSIS Development Effort Estimation Methodologies Wiki on TechNet.