Error importing data from oracle database to an SQL database

Hi!

When i was importing a database table from an Oracle Database to a SQL database table, the wizard returns this error:

Could not connect source component.

Warning 0x80202066: Source - VB_PERMISSIONS [1]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

Error 0xc0204018: DTS.Pipeline: The "output column "PE_ACTIVE" (34)" has a precision that is not valid. The precision must be between 1 and 38.
(SQL Server Import and Export Wizard)

I tried to change the destination type field to others than the default (decimal), but noting works.

Anyone can help me please?

Thx a lot

Cristovao

I'm currently working on a project that pull data from an Oracle DB and have had not problems pulling data and loading it into SQL 2005.
As far as the codepage error, this occurs when you are pulling non-unicode string values from Oracle(and other DBs as well). SSIS cannot retrieve the CodePage (character set) used on the Oracle DB. To eliminate the warning (assuming Western Alphabet) just set the OLE DB Source to AlwaysUseDefaultCodePage to True. The DefaultCodePage should be set to 1252 which is correct for Western Alphabet. If Oracle is not using the Western Alphabet, you'll need to determine the charset used and use http://msdn.microsoft.com/library/default.asp?url=/workshop/database/tdc/reference/CharSet.asp to correctly set the default code page.
As far as the PE_ACTIVE output column error, I can't tell what the error is. If you let me know how field is defined in Oracle, I may be able to help you. Also what version of Oracle are you using?
Larry
|||Cristovao,

Could you tell us what is the source (Oracle) data type and precision and what is the suggested destination (SQL Server) type and precision?

Thanks.|||

Hi

Thx a lot for your answer.

I am sending to you the data that you asked me:

Type of the source fied: Number (1)
Suggested destination : Decimal (1)
Oracle version: 8.1.7
Best Regards,
Cristovao

|||The value 1 should be a valid precision.

Could you do another check for me? Please select the failing table on the Select Source Tables and Views" page and click "Edit..." on it. In the grid that shows up, find your "PE_ACTIVE" column and take a look at the values for Size, Precision and Scale.
There should be only the precision with the value 1.|||Hi Bob!

Thx again for your interest...

Yes, i have checked and in fact there are only the precision with the value 1, but the error occurs... |||Well, it sounds intriguing.

Can you tell me what OLE DB driver you used; Microsoft OLE DB for Oracle or Oracle OLE DB?

I was able to copy a Number(1) column without any problem, using the MS driver.

Also, what version of SSIS do you have installed?
Thanks.|||

Hi Bob!

Well, i used the Oracle Provider For OLE DB... After have read your post i tried the Microsoft OLEDB Provider For Oracle, and the data have been imported, returning this warning:
TITLE: SQL Server Import and Export Wizard

Warning 0x80202066: Source - VB_PERMISSIONS [1]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

BUTTONS:

OK
I suppose it is normal.

Thanks a lot for your help.

Best Regards,
Cristovao

|||I am glad you were able to make it work.

Yes, I believe this warning can be safely ignored in this case.|||Hi all!

Same problem that I had, but the problem was that Oracle did not have a size or scale specified. SSIS automatically treats all of there as numerics, and with no specification on the precision or scale, it blows up. I have found no way to set it, and have tried both the Microsoft and Oracle drivers. Nada!

The good news....just use a Data Reader connection with the .Net Oracle Client. Works like a champ!!!!!!!!

Scott|||

Larry_Pope wrote:

As far as the codepage error, this occurs when you are pulling non-unicode string values from Oracle(and other DBs as well). SSIS cannot retrieve the CodePage (character set) used on the Oracle DB. To eliminate the warning (assuming Western Alphabet) just set the OLE DB Source to AlwaysUseDefaultCodePage to True. The DefaultCodePage should be set to 1252 which is correct for Western Alphabet. If Oracle is not using the Western Alphabet, you'll need to determine the charset used and use http://msdn.microsoft.com/library/default.asp?url=/workshop/database/tdc/reference/CharSet.asp to correctly set the default code page.

Thanks for this Larry - AlwaysUseDefaultCodePage=TRUE worked a treat!

-Jamie|||Here is the latest issue I found. Some Oracle tables do not have a precision or scale assigned to the column when the type is set to NUMBER. This causes an error when using an OLEDB connection. Has anyone found a way to get around this? The DataReader does it fine, but I don't think it is as fast as the DataReader.

Thanks!

Scott Barrett|||Any luck on thos problem, Scott?|||Here is the issue. When using SSIS against Oracle 8i or any database imported from 8i, when a column was meant to be what we call an INT, they call NUMBER and do not set the precision or scale. Oracle 9i and up does this by default. This will cause SSIS to not be able to bring in data from those columns. So we now have two solutions: Use the Data Reader Source and your done, or use the OLE DB Source and only using the Advanced Editor, enter the source and manually create each output column manually. If you are pulling from a complete table instead of a query, you can manually change the datatype in the advanced editor to a DT_I4 and it will then work.

Hope this helps you out!

Scott Barrett
Moffitt Cancer Center
|||I have installed sql server 2005 evaluation version and have oracle 8i.
But I can't find OLE DB Source property of AlwaysUseDefaultCodePage to set it to True.
Can someone help me and show me directions.

Thanks,
Mitja