locked
Legacy DTS Package running in SQL 2005 cannot connect to Mainframe DB2 DB via ODBC RRS feed

  • Question

  •  We are currently moving DTS packages into our SQL 2005 environment without converting them to SSIS packages (time constraint).  I have a ODBC DSN connection to a mainframe DB2 database in the package.  When I run the package from my desktop it runs successfully.  But when I run it as a job on the server, it gives me the below message:


    Message
    Executed as user: AZCORP\YSVBDBC. ...ecuting...   DTSRun OnStart:  DTSStep_DTSDataPumpTask_1   DTSRun OnError:  DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)      Error string:  [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified      Error source:  Microsoft OLE DB Provider for ODBC Drivers      Help file:        Help context:  0      Error Detail Records:      Error:  -2147467259 (80004005); Provider Error:  0 (0)      Error string:  [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified      Error source:  Microsoft OLE DB Provider for ODBC Drivers      Help file:        Help context:  0      DTSRun OnFinish:  DTSStep_DTSDataPumpTask_1      Error:  -2147220440 (80040428); Provider Error:  0 (0)      Error string:  Package failed because Step 'DTSStep_DTSDataPumpTask_1' failed.      Error source:  Microsoft Data Transformation Services (DTS) Package      Help file:  sqldts80.hlp      Help context:  .  The step failed.


    I've verified that the DSN is setup on the new server correctly.  I found an article on the internet that indicated there maybe an issue because the DTS package was trying to find the 32 bit ODBC but the 64 bit ODBC actually had the DSN connection in it.  When I took a look at the file C:\Windows\SysWOW64\odbcad32.exe it did not contain the DSN for the db connection.  So, any idea how I can get the DTS package to use the 64 bit ODBC connection?  Or what's the best way, without converting this package, can I get my legacy DTS package to run on my new server (sql server 2005).

    Thanks,

    Yo.

      
    Friday, November 7, 2008 11:07 PM

Answers