Error importing data from oracle database to an SQL database

Error importing data from oracle database to an SQL database

• CodeBash

  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...
  
  
  
  

• Beamer

  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

  
  
  

• Per Kristian Hagen

  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
  
  
  

• kcnz

  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.
  
  

• ShaunO

  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
  

  
  
  

• swdeveloper

  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.
  
  

• Richard.C.Bower

  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
  
  

• 57Ford

  Any luck on thos problem, Scott
  
  

• Azim Ghoniem

  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
  
  

• 4986

  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
  

• David Brenchley

  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.
  
  

• Bogdan Crivat-MS

  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
  

• D N

  I am glad you were able to make it work.
  
  Yes, I believe this warning can be safely ignored in this case.
  
  

• Gliksman

  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
  
• Error importing data from oracle database to an SQL database