none
SSIS Data load issue with CLOB datatype. RRS feed

  • Question

  • Hello,

              we are facing an issue while loading the data of one column KOMMENTARE (CLOB datatype) from Oracle database to SQL database by using SSIS 2008.

    By Corresponding to that column (which is using CLOB datatype), we have used DT_NTEXT in our application package.

    As the data load is working properly, the load is getting failed all of sudden by specifiying the issue as mentioned below.

    [Read from ORACLE PMT_ABC_TAB1] Error: Failed to retrieve long data for column "KOMMENTARE".

    [Read from ORACLE PMT_ABC_TAB1[1]] Error: There was an error with output "OLE DB Source Output" (11) on component "Read from ORACLE PMT_ABC_TAB1" (1). The column status returned was: "DBSTATUS_UNAVAILABLE".

    Could you please let us know the reason for these kind of issues?.

    Please help us in resolving this issues.

    Thanks in advance!!.. :-)

    Regards,

    Pradeep.

    Tuesday, April 24, 2012 10:13 PM

All replies

  • Hi Pradeep,

    Are you using Microsoft OLE DB Provider for Oracle to connect to oracle? The Microsoft OLE DB Provider for Oracle does not support the Oracle BLOB, CLOB, NCLOB, BFILE, and UROWID data types. Therefore, the OLE DB source cannot extract data from tables that contain columns with these data types. I suggest you can use .NET data providers for Oracle to connect to Oracle, for more information about it, please see: http://msdn.microsoft.com/en-us/library/ms971518.aspx 

    CLOB is mapped to VARCHAR(MAX) in SQL Server, please refer to the following link:
    Data Type Mapping for Oracle Publishers: http://msdn.microsoft.com/en-us/library/ms151817(v=sql.100).aspx

    Thanks,
    Eileen


    Thursday, April 26, 2012 8:18 AM
  • Hello Eileen,

                        Thanks for your reply!!..In our application, we are using the OLEDB provider for oracle to extract the data from Oracle database.

    One question!!..If the OLEDB provider is not supporting the datatype which are related to CLOB, then how it has started loading the data for few records?

    As part of the data load activity, we are able to transfer few records and in the middle, the data load got failed by mentioning the error.

    Failed to retrieve long data for column "KOMMENTARE".

    Please provide your view on the same.

    Thank you in advance!!..:-)

    Thank you!!

    Regards,

    Pradeep.

    Friday, April 27, 2012 12:00 AM
  • Hi Pradeep,

    If you are using the oracle connectors win32_11gR2_client and win64_11gR2_client, they don't support metadata neither unicode.

    Solution:

    In oracle convert to varchar2 and then convert again to ascii to be supported by the connector with the function:

    convert(dbms_lob.substr(KOMMENTARE, 2000, 1),'US7ASCII','WE8MSWIN1252')

    In the convertion function Use the same DefaultCodePage you will use in SSIS.

    https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions027.htm

    In SSIS package at the properties of OLEDB Source, Select AlwaysUseDefaultCodePage as True.

    If needed, use a Data conversion in SSIS to convert to Unicode.

    Regards

    Ricardo Teixeira




    • Edited by JRicardoCT Tuesday, July 5, 2016 3:18 PM
    Tuesday, July 5, 2016 3:12 PM