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

Question

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

Answer 1

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

Answer 2

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?

Answer 3

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