MS SQL Server DTS - Connection to Data Source fails in Job

Author

Connection to Data Source fails in Job

Jim Abel

2005-09-26, 11:23 am

i I have set up a DTS job that pulls data from an Oracle Database It has
worked for more than a year using the Oracle 8i Clients and and the Microsoft
OLE DB Provider for Oracle.
I installed the Oracle 9i Client and tested the DTS and it worked. I then
removed the Oracle 8i Client and test the DTS and it failed. I messed up
with the earlier test thinking that it was using the 9I Client when it
actually was still using the 8i.
I then retested the Oracle 9i client and it tested successful. I then went
to the Micorosoft DataSources to validate the Microsoft OLE DB Provider for
Oracle and it was not to be found. Apparently when I removed the Oracle 8i
client ot was also removed. I had the option to select the Oracle OLE DB
provider configured it and tested it sucessfully with the option to save the
user and password. I then went to the DTS and modified the connection
properties to use the new Data Source and manually executed the DTS Package
sucessfully. at this point I waited for the Scheduled Job task to run and if
failed. I then deleted the job. Opened the DTSRunUI utility and created a
new job. The next day the task failed. I have Loged the DTS package and get
the following error in the log.

Step Error Source: Microsoft OLE DB Provider for ODBC Drivers
Step Error Description:[Microsoft][ODBC Driver Manager] Driver's
SQLAllocHandle on SQL_HANDLE_ENV failed
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:0

What am I doing wrong?

Felix_Jiang

2005-09-27, 3:23 am

Seems you have changed your data source connection to use Oracle OLE DB
provider. But as what the error message implies, the Error Source is
Microsoft OLE DB Provider, which, I think, should at least be Oracle OLE DB
provider. Did you saved your package after you make the change?
I encouter the same problem with data connection to oracle when working in
DTS using Microsoft OLE DB. When switch to oracle OLE DB Provider, the
problem is gone. But I never encouter the problem again with Service Pack 3
applied

Jim Abel

2005-09-28, 11:24 am

Your note gave me a clue to look more closely to the connection object of the
DTS package. I found that when I attempted to modify the existing connection
did not work so I tried setting a new connection configured it and then was
not sure what to do with the transfom dialog that came up I tried selecting
the task in one case and the package seems to hang up when I run the job. In
the other case I just closed the dialog and when the Job was run it fails on
the step that connects with the Oracle database. The error in the Package Log
is as follows:

Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:The Data Pump Task requires Transformations to be
specified.
Step Error code: 80040408
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:4700

What does this mean?

"Felix_Jiang" wrote:

> Seems you have changed your data source connection to use Oracle OLE DB
> provider. But as what the error message implies, the Error Source is
> Microsoft OLE DB Provider, which, I think, should at least be Oracle OLE DB
> provider. Did you saved your package after you make the change?
> I encouter the same problem with data connection to oracle when working in
> DTS using Microsoft OLE DB. When switch to oracle OLE DB Provider, the
> problem is gone. But I never encouter the problem again with Service Pack 3
> applied

Sponsored Links