Connecting to Oracle RDB from SSIS

Hi Gurus,

I am trying to build a Data Warehouse using SSIS in SQL Server 2005. My source data is in Oracle RDB. Now when I am trying to connect to oracle RDB using the OLE DB Source component, I am getting the following error.

Test connection failed because of an error in initializing provider. Oracle client and networking componenets were not found. These components are supplied by Oracle Corporation and part of the Oracle Version 7.3.3 or late client software installtion.

Provider is unable to funtion until these components are installed.

I did install the Oracle RDB client software .Apparently it looks like Microsoft OLE DB for Oracle supports Oracle 7.3.3 and above. At the same time I tried to use ODBC for Oracle RDB driver from ODBC data sources. But the OLE DB Source Component in SSIS can't recognize ODBC. So now I am stuck on how to load the data from Oracle RDB to SQL Server 2005 staging area.

Any thoughts on this would be really appreciated. Ideally we are looking to port the data directly from Oracle RDB to SQL Server without any intermediate flat files (flat files etc)

Thanks,
SK

sk3636 wrote:

Hi Gurus,

I am trying to build a Data Warehouse using SSIS in SQL Server 2005. My source data is in Oracle RDB. Now when I am trying to connect to oracle RDB using the OLE DB Source component, I am getting the following error.

Test connection failed because of an error in initializing provider. Oracle client and networking componenets were not found. These components are supplied by Oracle Corporation and part of the Oracle Version 7.3.3 or late client software installtion.

Provider is unable to funtion until these components are installed.

I did install the Oracle RDB client software .Apparently it looks like Microsoft OLE DB for Oracle supports Oracle 7.3.3 and above. At the same time I tried to use ODBC for Oracle RDB driver from ODBC data sources. But the OLE DB Source Component in SSIS can't recognize ODBC. So now I am stuck on how to load the data from Oracle RDB to SQL Server 2005 staging area.

Any thoughts on this would be really appreciated. Ideally we are looking to port the data directly from Oracle RDB to SQL Server without any intermediate flat files (flat files etc)

Thanks,
SK

I think you should use the ADO.Net connection manager to connect to ODBC sources.

-Jamie

|||

There is an existing post:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=640502&SiteID=1

which could help you out.
Overall, first, you need to install Oracle client. Second, you need to configure tnsnames.ora file.

Regards,
Yitzhak

|||

Hi,

I was able to make a connection to Oracle RDB using ADO.net. But when I used the ADO.net connection, I am getting the following error for one of the columns in Data Source Reader component.

The phone data type for the external column and Output column is Unicode string [DT_WSTR] and SSIS is not letting me change the data type on the output column. The phone column is defined as varchar in SQL Server 2005.

Error: 0xC020902A at Data Flow Task, DataReader Source [1736]: The "component "DataReader Source" (1736)" failed because truncation occurred, and the truncation row disposition on "output column "PHONE" (2469)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

Error: 0xC02090F5 at Data Flow Task, DataReader Source [1736]: The component "DataReader Source" (1736) was unable to process the data.

Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "DataReader Source" (1736) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.

Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.

Thanks,
SK

|||

sk3636 wrote:

Hi,

I was able to make a connection to Oracle RDB using ADO.net. But when I used the ADO.net connection, I am getting the following error for one of the columns in Data Source Reader component.

The phone data type for the external column and Output column is Unicode string [DT_WSTR] and SSIS is not letting me change the data type on the output column. The phone column is defined as varchar in SQL Server 2005.

Error: 0xC020902A at Data Flow Task, DataReader Source [1736]: The "component "DataReader Source" (1736)" failed because truncation occurred, and the truncation row disposition on "output column "PHONE" (2469)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

Error: 0xC02090F5 at Data Flow Task, DataReader Source [1736]: The component "DataReader Source" (1736) was unable to process the data.

Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "DataReader Source" (1736) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.

Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.

Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.

Thanks,
SK

So when you said "SSIS is not letting me change the data type on the output column" that's not actually true is it? It HAS let you change it and at execution-time you are now getting these errors.

The error is pretty self explanatory. You are trying to push a value into a column in the pipeline that is not wide enough for it. Hence, you should widen the column.

-Jamie

|||

Hi Jamie,

It HAS let you change it and at execution-time you are now getting these errors.

Not really. I just changed the property in the output column properties and when I say OK, it throws the following error.

Error at Data Flow Task [DataReader Source [4188]]: The data type of output columns on the component "DataReader Source" (4188) cannot be changed.

Error at Data Flow Task [DataReader Source [4188]]: System.Runtime.InteropServices.COMException (0xC020837D)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.SetOutputColumnDataTypeProperties(Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostSetOutputColumnDataTypeProperties(IDTSManagedComponentWrapper90 wrapper, Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)

Thanks,
SK

|||

sk3636 wrote:

Hi Jamie,

It HAS let you change it and at execution-time you are now getting these errors.

Not really. I just changed the property in the output column properties and when I say OK, it throws the following error.

Error at Data Flow Task [DataReader Source [4188]]: The data type of output columns on the component "DataReader Source" (4188) cannot be changed.

Error at Data Flow Task [DataReader Source [4188]]: System.Runtime.InteropServices.COMException (0xC020837D)
at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.SetOutputColumnDataTypeProperties(Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)
at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostSetOutputColumnDataTypeProperties(IDTSManagedComponentWrapper90 wrapper, Int32 iOutputID, Int32 iOutputColumnID, DataType eDataType, Int32 iLength, Int32 iPrecision, Int32 iScale, Int32 iCodePage)

Thanks,
SK

Oh OK, sorry. In that case you will have to use a Data Conversion component or Derived Column component to convert to the datatype that you wish to convert to.

-Jamie

|||

I did try to use the Data conversion transformation and I got the same error. The error is coming at the output column from the data source reader. So the data flow component didn't traverse upto Data Conversion component to check whether the conversion is taking place or not.

I found similiar threads related to the same issue that I am facing.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=392224&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=278744&SiteID=1

SK

|||Have you or anyone else found a resolution for this? I'm experiencing the same problem.