How to load data from Oracle CLOB to SQL Server VARCHAR(MAX) using SSIS

Dj 1 Reputation point
2020-12-17T08:05:58.803+00:00

I need help in loading data from clob datatype to SQL Server Varchar(max) using SSIS. I tried below methods nothing works as expected

  1. DBMS_LOB.substr("ColumnName",4000,1)
    Error: Data gets truncated to 4000 as my source has 10000 bytes
  2. DBMS_LOB.substr("columnname",4000,1)||DBMS_LOB.substr("columnname",8000,4001)
    Error: SSIS gives error as concatenation is too long
  3. TO_CHAR(ColumnName)
    Error: OCI error encountered. ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion
  4. Changed the code page to UTF-8 and changed the datatype
    Error: Cannot convert columnname from unicode to non unicode

Please suggest any workable solution

-Dj

Azure SQL Database
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,466 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,562 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Yitzhak Khabinsky 25,201 Reputation points
    2020-12-17T13:32:38.037+00:00

    If your targeted SSIS version is SQL Server 2019 Enterprise Edition, you can use Microsoft Connector for Oracle. Behind the scene it is using Progress DataDirect Oracle Wire Protocol ODBC driver, v.8.0.2
    Download link: Microsoft Connector for Oracle

    Amazingly enough, there is no need to install Oracle Client, Oracle homes, TNSes, etc.

    That driver supports your environment data types, including Oracle XMLType data type.
    You can check its all supported features here: Oracle Wire Protocol ODBC driver


  2. Monalv-MSFT 5,896 Reputation points
    2020-12-18T02:07:17.64+00:00

    Hi @Dj ,

    Welcome to Microsoft Q&A Platform. Thanks for posting here.

    Could you please share the components that you used in SSIS package?

    1.We can use Oracle source and Microsoft Connector for Oracle provider in Oracle Connection Manager to load oracle data in SSIS package.

    2.We can use Data Conversion Transformation to convert the data types of columns in SSIS Data Flow Task.

    Best Regards,
    Mona


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    Hot issues in November--What can I do if my transaction log is full?
    Hot issues in November--How to convert Profiler trace into a SQL Server table?


  3. Ronen Ariely 15,096 Reputation points
    2020-12-22T09:20:27.383+00:00

    Good day,

    Theory

    As of SQL Server 2019 we have support for Unicode with encoding of UTF-8. This supports using a simple VARCHAR data type, which is a game changer (this is basically the same we have in Oracle). Therefore, using SQL Server 2019 and above the procedure is simple and you just need to use the right connector. The target table can use UTF-8 using data type VARCHAR

    Before SQL Server 2019 there was no native support to store data in UTF-8. The older servers use only UTF-16 using a special data type NVARCHAR. This make everything more challenging and this is the source of your issue. The SSIS read the data from Oracle and try to use VARCHAR in the target but the source is UNICODE and VARCHAR before 2019 does not support Unicode. Therefore, the implicit convert between the source Unicode to the target non-unicode fails.

    The solution is to explicitly "inform" the service that your target is NVARCHAR and not VARCHAR, and this is done using explicit CONVERT.

    What next?

    Option #1: Add Data Conversion transformations to convert all the string columns from non-Unicode (DT_STR) to Unicode (DT_WSTR) strings (meaning add a data conversion block into your data flow diagram).

    Option #2: Try this:

    1). right click on source task and select "Show Advanced editor"
    2). Go to "Input and Output Properties" tab and select the string output columns
    3). Change the data type from "String[DT_STR]" into "Unicode String[DT_WSTR]".

    Please inform us if this solve your needs