November 3, 2008 at 5:14 am
Hi everyone,
We are using an SSIS package to transfer data from some replicated Oracle Databases to an SQL Server 2005 Database.
We are currently facing the following problem:
We have created the two OLE DB connection managers (One for the Oracle first instance and another one for the SQL Server database).
We have also created the data flow task, from the oracle source table to the Sql Server destination table. The
The task is executing perfectly.
To dynamically access the other Oracle instances, we have created some package variables which we are populating with the right instance name and oracle user/pwd at runtime. The ConnectString Expression is set to the targeted database using the passed parameters, like this:
"Data Source="+@[User::SRC_INSTANCE_NAME]+";User ID="+@[User::SRC_USER_NAME]+";Password="+@[User::SRC_PWD]+";Provider="+@[User::SRC_PROVIDER]+";" +"Persist Security Info=True;"
SSIS is able to connect to the instance used to create the connection manager, but when we change the connection to reach anyone of the other instances , the data flow task failed and we are getting messages such as
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "ORA-01403: no data found".
Error: 0xC0047038 at ......., DTS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.
We suspect a problem with the Oracle OLE DB Connection....
Has someone encountered a similar problem?
Thanks fro your help.;)
Dany
November 4, 2008 at 10:23 am
Dany (11/3/2008)
Hi everyone,We are using an SSIS package to transfer data from some replicated Oracle Databases to an SQL Server 2005 Database.
We are currently facing the following problem:
We have created the two OLE DB connection managers (One for the Oracle first instance and another one for the SQL Server database).
We have also created the data flow task, from the oracle source table to the Sql Server destination table. The
The task is executing perfectly.
To dynamically access the other Oracle instances, we have created some package variables which we are populating with the right instance name and oracle user/pwd at runtime. The ConnectString Expression is set to the targeted database using the passed parameters, like this:
"Data Source="+@[User::SRC_INSTANCE_NAME]+";User ID="+@[User::SRC_USER_NAME]+";Password="+@[User::SRC_PWD]+";Provider="+@[User::SRC_PROVIDER]+";" +"Persist Security Info=True;"
SSIS is able to connect to the instance used to create the connection manager, but when we change the connection to reach anyone of the other instances , the data flow task failed and we are getting messages such as
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "ORA-01403: no data found".
Error: 0xC0047038 at ......., DTS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.
We suspect a problem with the Oracle OLE DB Connection....
Has someone encountered a similar problem?
Thanks fro your help.;)
Dany
The first thing with oracle is always to look up the ora message, in this case
http://www.orafaq.com/wiki/ORA-01403
You ran the command correctly, but returned no data. You need to be sure what ever is being run is correct for that instance. I suspect it is not, either due to security or incorrect naming. Can't help any more without specifics.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply