January 6, 2010 at 12:57 pm
Hi,
I want to export a table in SQL Server to Oracle. I have created an exactly similar table with the correct data types in the Oracle database. Then I tried to export the data from SQL Server table to the Oracle table.
But some of the DATA (in a bunch some of the columns like Customer Name , User Name etc.. i.e. First name, Last name) in the SQL Server table has commas in it. When I am using SSIS or Import\Export Wizard to export the data, I am getting errors due to these commas in the data.
For those columns with commas in it I am getting the following error:
[OLE DB Destination [1117]] Error: 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-12899: value too large for column "SCHEMA_NAME"."TABLE_NAME"."COLUMN_NAME" (actual: 34, maximum: 32)".
I have increased the destination column width to 34. But no use.
Even when I try to export data into a CSV file I am getting the data out of sync ?
Could any one please help me?
Best Regards,
SQL Buddy.
January 6, 2010 at 2:12 pm
I don't think your error has anything to do with whether or not your data has commas in it. The error is very clear - you are attempting to insert data into a column that is defined as 32 characters where the column data is 34 characters.
[OLE DB Destination [1117]] Error: 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-12899: value too large for column "SCHEMA_NAME"."TABLE_NAME"."COLUMN_NAME" (actual: 34, maximum: 32)".
So, this tells me that your column definitions are out of synch between SQL Server and Oracle. Validate each columns definition against the source system and make sure they match.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 6, 2010 at 4:09 pm
Hi Jeffrey,
You are absolutely correct. Thank you very much for your help.
I changed the width of the destination columns and it succeded.
Thank You,
Best Regards,
SQL Buddy
January 6, 2010 at 7:05 pm
That is good to hear - thanks for the feedback.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply