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 lotCristovao

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.

HiThx 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.7Best 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 BarrettMoffitt 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

Mali Buda wrote: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,MitjaMitja,1) Go into your data-flow that contains your OLE DB Source component.2) Click the OLE DB Source component once3) Press F4The properties pane will appear showing all the properties of the OLE DB Source component. In here you will find AlwaysUseDefaultCodePage.-Jamie

So after waaaayyyy too much testing, if you're going to get Oracle, here are your best options...1. ADO.Net DataReader with a ADO.Net for ODBC driver. Create a system DSN, and you're good to go. No problems with the NUMBER datatypes I mentioned above, as they are all converted to floats2. ADO.Net DataReader with the ADO.Net Oracle Client - Not as fast, and all numbers with decimal places will be cut off.3. OLE DB connection - Use the Microsoft Driver first. It at least sets the precision and scale for you. It is wrong if you have decimal places, but you can adjust that in the advanced editor.4. OLE DB Connection with Oracle OLE DB Driver - this is pure luck if it works, and most number fields will bomb out! You can manually creat every column from scratch in the advanced editor, but by the time you get to the third column, you'll switch to one of the other methods....oh yeah...this is the slowest read times as well.....Scott Barrett

Jamie,I have tried that, but it seems that my properties does not include this property. It doesn't matter if I use Oracle Ole DB provider or if I use MS Oracle provider.OLE DB Source does not show this property.Maybe it's the version of MS SQL Server 2005 (evaluation Build 9.00.1116 on April CTP Release)Been searching for update for sql server 2005, but couldn't find it anywhere.What else can I do?If I try using import wizard I get the same error message for code page.Thanks anyway and best regardsMitja

Mitja...You are MULTIPLE versions behind. Get the released version! April was as buggy as any release they have had.You'll find you answer in the new version. Download the developer edition and give it a try....Scott Barrett

I have tried all of the methods, with non of them working! Is microsoft looking at a fix. No problem pulling data with SQL 2000 DTS. Please Help!

Scott,please don't forget to mention, that if you are using ADO.Net DataReader from Oracle anycharacter code will be converted to unicode internally. For those who use different code pagesin SQL this means a lot of unnecessary work on converting unicode to the appropriate code page.I use OLEDB where you can determine the codepage in the properties of the DataSource.Fridtjof

Fridtjof.... We have moved away from the ADO.Net DataReader as it cannot except expressions for the SQL statement. We are using the OLE DB source, however it has the one drawback of not reading the scale of numbers correctly. You manually have to change the output columns to contain the correct precision and scale. After that, everything has been fine. We typically move 100gb's of data daily with no problem using this. The key is to use the Microsoft OLEDB for Oracle driver for any reading and writing you do. The only exception is on the SCD transformation, particularly the OLEDB command associated with the changed record update. That needs to use the Oracle OLEDB driver, as the Microsoft one does not handle the parameters well. As for writing to the database, I use the new OracleDestination from Persistent. In a word...FAST!!!!! Millions of rows in a minute. Scott Barrett scott@barrettzone.com http://microsoftdw.blogspot.com

The problem is when it pulling data from oracle with a datetime field of :9999-01-01 00:00:00.000. It fails! With sql 2000 server DTS it moved the data with no failers! oracle : ole db Source (microsoft provider for Oracle: MSDAORA.1) SQL : ole db Destination (Provider SQLNCLI.1) Error 0xc0204018: DTS.Pipeline: The "output column " " has a precision that is not valid. The precision must be between 1 and 38. (SQL Server Import and Export Wizard)

that is a different error than a date. Check the advanced editor on the component. I would be the precision on one of the columns that is numeric is set to 0. Scott

I retested and this is what I am getting: with pulling one record from the oracle DB! Error 1: [OLE DB Destination [16]] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Error 2: (Source data value from oracle with Datetime: 9999-01-01 00:00:00.000)[OLE DB Destination [16]] Error: There was an error with input column "T$CRDD$C" (142) on input "OLE DB Destination Input" (29). The column status returned was: "Conversion failed because the data value overflowed the specified type.". Error 3:[OLE DB Destination [16]] Error: The "input "OLE DB Destination Input" (29)" failed because error code 0xC020907A occurred, and the error row disposition on "input "OLE DB Destination Input" (29)" specifies failure on error. An error occurred on the specified object of the specified component. Error 4:[DTS.Pipeline] Error: The ProcessInput method on component "OLE DB Destination" (16) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. Error 5:[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0209029. When I preview the data it shows <value to big to display>When preview in SQL2000 DTS it shows the value '9999-01-01 00:00:00.000'! What Can I do to fix this issue.Thanks for all the help so far. Trevor

Tevor, When you are executing the query to get the data, do a TO_CHAR and convert it to a string and then convert it back when you write it to the destination.... Scott

AlwaysUseDefaultCodePage=TRUE worked for me for OLEDB source but it didn't work for lookup. now lookup has that warning, but no option to select "AlwaysUseDefaultCodePage=TRUE". So what I do now?......

In my case, the message about precision is not a warning message, but a Error message, so I cant click OK to continue. I have already set UseDefaultCodePage=true, but the error about numeric fields continue. Does any one knows how to solve it?

TKDev wrote:I retested and this is what I am getting: with pulling one record from the oracle DB! Error 1: [OLE DB Destination [16]] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Error 2: (Source data value from oracle with Datetime: 9999-01-01 00:00:00.000)[OLE DB Destination [16]] Error: There was an error with input column "T$CRDD$C" (142) on input "OLE DB Destination Input" (29). The column status returned was: "Conversion failed because the data value overflowed the specified type.". Error 3:[OLE DB Destination [16]] Error: The "input "OLE DB Destination Input" (29)" failed because error code 0xC020907A occurred, and the error row disposition on "input "OLE DB Destination Input" (29)" specifies failure on error. An error occurred on the specified object of the specified component. Error 4:[DTS.Pipeline] Error: The ProcessInput method on component "OLE DB Destination" (16) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. Error 5:[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0209029. When I preview the data it shows <value to big to display>When preview in SQL2000 DTS it shows the value '9999-01-01 00:00:00.000'! What Can I do to fix this issue.Thanks for all the help so far. TrevorHi Trevor,I 've the same problem, how dit you solved this problem. Thanks in advance.Olaf

What you did to solve the problems because i have the same problems ? No one have clearly explain how to solve this is issue on then lookup component !

I have the some problem when iget thedata from flat file to Oracle. I have changed the "AlwaysUseDefaultCodePage" to "True", and there is no warning.But i find when i run the package,seems there is a little longer time to finish(turn green) the using SQL SERVER as the destination.Anybody can tell me why? Thanks a lot. John.Wu

John.Wu wrote: I have the some problem when iget thedata from flat file to Oracle. I have changed the "AlwaysUseDefaultCodePage" to "True", and there is no warning.But i find when i run the package,seems there is a little longer time to finish(turn green) the using SQL SERVER as the destination.Anybody can tell me why? Thanks a lot. John.Wu Inserting to Oracle isn't particularly quick with SSIS. Read more here: Oracle and SQL Server Integration Services (http://www.sqljunkies.com/WebLog/donald_farmer/archive/2005/03/13/8819.aspx) Persistent do a driver that can be used with SSIS to speed up this process. Persistent Oracle Connector (http://blogs.conchango.com/jamiethomson/archive/2006/04/07/SSIS_3A00_-Persistent-Oracle-Connector.aspx) -Jamie

Can you please tell us where we can download/obtain this driver (OracleDestination from Persistent)? Thanks.

BHBD wrote: Can you please tell us where we can download/obtain this driver (OracleDestination from Persistent)? Thanks. Ever heard of search engines? http://search.live.com/results.aspx?q=persistent+ssis+oracle&form=QBNO

I know this is an old thread but I am having the issue in SQL 2008 when using import. I have an Oracle 8i DB that I am attempting to import a table into SQL 2008 and it fails with the same error Can you tell me how to fix this issue in SQL 2008? Thanks.