Quick Tip: Resolving Oracle Destination Datatype conversion errors

When working with the Oracle Destination (the one from Microsoft/Attunity (2008 | 2012)), you might encounter generic data type conversion errors after mapping your columns.

image

image

Validation error. Data Flow Task: Data Flow Task: Datatype conversion of Oracle Destination.Inputs[Oracle Destination Input].Columns[L_QUANTITY] is not supported.

The error appears because there is not a 1:1 mapping of SSIS Data Flow types to Oracle types, and the Oracle Destination can’t do the conversion for you. A common one is for BIGINT conversions. Unfortunately, the error message doesn’t provide much guidance on what the type mismatch is, but there is an easy way to check.

Right click on the Oracle Destination component, and select Show Advanced Editor …

On the Input and Output Properties tab, expand the Oracle Destination Input list item, and view the external columns.

image

Click the column which is using you the error. On the properties page, you’ll see the SSIS data type that the destination expects.

image

In your data flow, use a Data Conversion or Derived Column transform to convert the original column to the destination data type.

Presenting at Tech Days Hong Kong 2013

I’ve been invited to the Microsoft Tech•Days event in Hong Kong next month – March 12th to 14th. This will be my first trip to Hong Kong, and I’m really looking forward to presenting to an international audience. I’ll be doing three SSIS related sessions:

  • EIM – Bringing Together DQS, MDS and SSIS
  • SSIS Performance Design Patterns
  • Using SQL Server Integration Services with Oracle

The EIM session will be similar to the ones I presented recently at the DevTeach Montreal and TechEd North America 2012 events, with a new end to end scenario that I recently worked on. The SSIS Performance Design Patterns talk will cover some of the patterns from the book, along with some brand new patterns for SQL 2012. The final session will cover using SSIS with Oracle, which is something I’ve never presented on before. I’ll be talking about the new CDC functionality for getting data out of Oracle, as well as the different connectivity options for getting data into Oracle (such as the high speed connectors in 2008/2012).

 

image