Moving TIMESTAMP column from Oracle to SQL Server

Question

I have an Oracle DB with some data that I want to move to a SQL Server.

Problem is that my Oracle DB have some columns with the type TIMESTAMP(0) WITH TIME ZONE and SSIS detects thous as CLOB. So it fails saying it cannot convert CLOB to datetime2.

I've already created the tables inside the SQL Server database. So it's just moving the data over with some type conversion.

I'm using the SQL Server Import and Export Wizard (SSIS) from SQL Server Management Studio (SSMS).

I'm using the .NET Framework Data Provider for Oracle to connect to the Oracle DB and the SQL Server Native Client 11.0 to connect to my SQL Server.

My source type is TIMESTAMP(0) WITH TIME ZONE and my destination type is datetime2.

Here's the error I'm getting:

[Source Information]
Source Location : localhost
Table: "MYSPACE"."MYTABLE"
Column: START_DATE
Column Type: CLOB
SSIS Type: Unicode text stream [DT_NTEXT]
Mapping file (to SSIS type): C:\Program Files (x86)\Microsoft SQL Server\140\DTS\MappingFiles\OracleClientToSSIS10.XML

[Destination Information]
Destination Location : localhost
Destination Provider : SQLNCLI11
Table: [dbo].[mytable]
Column: start_date
Column Type: datetime2
SSIS Type: database timestamp with precision [DT_DBTIMESTAMP2]
Mapping file (to SSIS type): C:\Program Files (x86)\Microsoft SQL Server\140\DTS\MappingFiles\MSSQLToSSIS10.XML

[Conversion Steps]
Conversion unknown ...
SSIS conversion file: C:\Program Files (x86)\Microsoft SQL Server\140\DTS\binn\DtwTypeConversion.xml

So as you can see the START_DATE column is detected as a CLOB. This is incorrect.

I looked inside OracleClientToSSIS10.XML

<!-- TIMESTAMP 10.* -->
<dtm:DataTypeMapping >
    <dtm:SourceDataType>
        <dtm:DataTypeName>timestamp</dtm:DataTypeName>
    </dtm:SourceDataType>
    <dtm:DestinationDataType>
        <dtm:NumericType>
            <dtm:DataTypeName>DT_DBTIMESTAMP2</dtm:DataTypeName>
            <dtm:SkipPrecision/>
            <dtm:UseSourceScale/>
        </dtm:NumericType>
    </dtm:DestinationDataType>
</dtm:DataTypeMapping>  

<!-- TIMESTAMP WITH TIME ZONE 10.* -->
<dtm:DataTypeMapping >
    <dtm:SourceDataType>
        <dtm:DataTypeName>TIMESTAMP WITH TIME ZONE</dtm:DataTypeName>
    </dtm:SourceDataType>
    <dtm:DestinationDataType>
        <dtm:NumericType>
            <dtm:DataTypeName>DT_DBTIMESTAMPOFFSET</dtm:DataTypeName>
            <dtm:SkipPrecision/>
            <dtm:UseSourceScale/>
        </dtm:NumericType>
    </dtm:DestinationDataType>
</dtm:DataTypeMapping>  

<!-- CLOB -->
<dtm:DataTypeMapping >
    <dtm:SourceDataType>
        <dtm:DataTypeName>CLOB</dtm:DataTypeName>
    </dtm:SourceDataType>
    <dtm:DestinationDataType>
        <dtm:CharacterStringType>
            <dtm:DataTypeName>DT_NTEXT</dtm:DataTypeName>
            <dtm:Length>255</dtm:Length>
        </dtm:CharacterStringType>
    </dtm:DestinationDataType>
</dtm:DataTypeMapping>

Seems fine, right?

Answer 1

This answer is overdue, but at least I can add a working solution to this particular task. I had to do a task that was almost identical to yours, the only differences were the database versions and source data types (I had a TIMESTAMP with no time zone). I ran into the same driver error, failing to convert CLOB to datetime2.

Preface & Error Explanation

I noticed your source datatype, TIMESTAMP(0) WITH TIME ZONE, stores the time zone whereas your destination datatype, datetime2, does not. It doesn't really affect the conversion steps too much, but something to keep in mind.

In my troubleshooting, I also saw the OracleClientToSSIS10.XML file, and saw the same details you listed in your post. At first I thought the timestamp entry was invalid because it wasn't uppercase, but I confirmed it does work.

With the file import wizard, the importer will read from the source, bind to an SSIS datatype, convert to an intermittent SSIS datatype, then convert to a valid destination datatype. You'll have to look at it from SSIS's perspective when you read the error messages.

The OracleClientToSSIS10.XML is really what the file importer uses to bind the source datatype to a valid SSIS datatype. What looks like happened to you is that the source data type didn't exactly match what the importer expects for that data type, so it went with the next best thing, CLOB, which maps to DT_NTEXT. To see what the importer expects for DT_DBTIMESTAMPOFFSET, go here.

But this isn't what caused your error. If you look at the bottom of the error message, it lists DtwTypeConversion.xml This is the file that converts between SSIS datatypes.

C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\CommonExtensions\Microsoft\SSIS\150\Binn\DtwTypeConversion.xml

(filtered to just DT_NTEXT)

  <!-- Convert from DT_TEXT-->
  <dtw:ConversionEntry>
    <dtw:SourceType>DT_TEXT</dtw:SourceType>
    <dtw:DestinationType TypeName="DT_STR">
      <dtw:ConversionStep StepNum="1" ConvertToType="DT_STR"/>
    </dtw:DestinationType>
    <dtw:DestinationType TypeName="DT_WSTR">
      <dtw:ConversionStep StepNum="1" ConvertToType="DT_STR"/>
      <dtw:ConversionStep StepNum="2" ConvertToType="DT_WSTR"/>
    </dtw:DestinationType>
    <dtw:DestinationType TypeName="DT_IMAGE">
      <dtw:ConversionStep StepNum="1" ConvertToType="DT_IMAGE"/>
    </dtw:DestinationType>
    <dtw:DestinationType TypeName="DT_NTEXT">
      <dtw:ConversionStep StepNum="1" ConvertToType="DT_NTEXT"/>
    </dtw:DestinationType>
  </dtw:ConversionEntry>

  
  <!-- Convert from DT_NTEXT-->
  <dtw:ConversionEntry>
    <dtw:SourceType>DT_NTEXT</dtw:SourceType>
    <dtw:DestinationType TypeName="DT_STR">
      <dtw:ConversionStep StepNum="1" ConvertToType="DT_WSTR"/>
      <dtw:ConversionStep StepNum="2" ConvertToType="DT_STR"/>
    </dtw:DestinationType>
    <dtw:DestinationType TypeName="DT_WSTR">
      <dtw:ConversionStep StepNum="1" ConvertToType="DT_WSTR"/>
    </dtw:DestinationType>
    <dtw:DestinationType TypeName="DT_IMAGE">
      <dtw:ConversionStep StepNum="1" ConvertToType="DT_IMAGE"/>
    </dtw:DestinationType>
    <dtw:DestinationType TypeName="DT_TEXT">
      <dtw:ConversionStep StepNum="1" ConvertToType="DT_TEXT"/>
    </dtw:DestinationType>
  </dtw:ConversionEntry>

If you look under <!-- Convert from DT_NTEXT--> block, you'll notice that there's no subtree for DT_DBTIMESTAMP2, which means there's no conversion from DT_NTEXT to DT_DBTIMESTAMP2. This is what caused the error (DT_DBTIMESTAMP2 is the SSIS equivalent of datetime2; you can verify it here).

How to Fix the Problem You will need to either:

1. get the importer to recognize DT_DBTIMESTAMPOFFSET. To be honest, I don't think you'll get them to match up, others have stated the date time conversions have been incompatible or tricky
2. change your source query to pre-convert the TIMESTAMP into a string, then have the importer convert it to a datetime2. When you do this, you must ensure the DtwTypeConversion.xml file has a conversion from the string you chose, to the DT_DBTIMESTAMP2

System versions of listed solutions

Source database: Oracle 12c (12.1.0.2.0)

• Data types: mostly varchar2, numeric, and one field with timestamp(2) (no time zone)

Destination database: Microsoft SQL Server 2019

• Data types: smaller fields (varchar2 reduced, some were turned into char, some numeric were turned into int or smallint or tinyint). The timestamp field was turned into datetime2

SSMS: 18.9.1

Solution #1: Use SSMS - SQL Server Import and Export Wizard - Oracle Driver

1. Open up DtwTypeConversion.xml, go to the section where it says <!-- Convert from DT_NTEXT-->, and add a subtree, then save

      <!-- Convert from DT_WSTR-->
      <dtw:ConversionEntry>
        <dtw:SourceType>DT_WSTR</dtw:SourceType>
        <dtw:DestinationType TypeName="DT_I1">
          <dtw:ConversionStep StepNum="1" ConvertToType="DT_I1"/>
        </dtw:DestinationType>
   

   ...

        <dtw:DestinationType TypeName="DT_DBTIME">
          <dtw:ConversionStep StepNum="1" ConvertToType="DT_DBTIME"/>
        </dtw:DestinationType>
        <dtw:DestinationType TypeName="DT_DBTIMESTAMP">
          <dtw:ConversionStep StepNum="1" ConvertToType="DT_DBTIMESTAMP"/>
        </dtw:DestinationType>
        <dtw:DestinationType TypeName="DT_DBTIMESTAMP2">
          <dtw:ConversionStep StepNum="1" ConvertToType="DT_DBTIMESTAMP2"/>
        </dtw:DestinationType>
        <dtw:DestinationType TypeName="DT_FILETIME">
          <dtw:ConversionStep StepNum="1" ConvertToType="DT_FILETIME"/>
        </dtw:DestinationType>
   

2. Source driver:

• .NET Framework Data Provider for Oracle - ended up using this one, because it worked, was easy to build the connection string, and it required the least amount of effort to do the data type conversion.
• Microsoft OLE DB Provider for Oracle - This one could work, but the login procedure doesn't always work and it kept giving me problems with the text fields being Unicode/non-Unicode
• Oracle Provider for OLE DB - This one kept giving me errors with number conversions, so more effort to use

2. Destination driver:

• SQL Server Native Client 11.0

3. Create a source query that will convert the timestamp to a character, something akin to

    select
        allOtherFields
        ,TO_CHAR(TIMESTAMP '2021-08-02 14:30:20.05 -05:00','YYYY-MM-DD HH24:MI:SS.FF7') as converted_str
    from mySchema.myTable
   

• To test just a subset, you can add a FETCH FIRST 100 ROWS ONLY after your table name

4. When you review the mapping, you should see the timestamp field pass checks without any errors
5. Run and import

Solution #2: Use SSMS - SQL Server Import and Export Wizard, Flat File Source

1. Create a query that will convert the timestamp to a character, something akin to

    select
    allOtherFields
    --,TIMESTAMP '2021-08-02 14:30:20.05 -05:00' as original_timestamp_with_zone
    ,TO_CHAR(TIMESTAMP '2021-08-02 14:30:20.05 -05:00','YYYY-MM-DD HH24:MI:SS.FF7') as converted_str
    from mySchema.MyTable;
   

• To test just a subset, you can add a FETCH FIRST 100 ROWS ONLY after your table name
• Make sure the string output matches exactly to the datetime2 field you are going to. The example above should suffice

2. Save the query output to a csv file, it's a good idea to use a text qualifier
3. Source driver:

• Flat File Source
  • Ensure you set a text qualifier
  • Ensure the time column gets set as a string, or DT_WSTR

4. Destination driver:

• SQL Server Native Client 11.0

5. When you review the mapping, you should see the timestamp field pass checks without any errors
6. Run and import

Alternative: Use SSMA tool

The Microsoft docs will be a good guide for this. At first it seemed straightforward, but I ended up having to do quite a bit of code to prep the tables, as the code snippets generated don't get the indexes right and the data file would always default to [PRIMARY] Even after all the coding, I ended up scrapping this method because I was getting too many row errors and didn't get much info beyond the "table was partially processed". If you're just migrating smaller tables without much data type conversion, you'll be fine

Answer 2

I couldn't get any of the drivers to work for the date columns.

So I ended up going into SQL Developer and exporting the tables with date column to INSERT statements and then running regex replace on that.

Fun times :(

Answer 3

I've had similar problems dealing with Oracle-formatted date-ish datatypes. What I've found that works is to convert the data to a string on the Oracle side before pulling it across. Then you can transform the data once you get it into your SQL Server.

You'll want to modify the import/export wizard to use a query to specify the data to fetch from the Oracle server. As part of your source query, you can do the conversion like this:

SELECT
CAST("START_DATE" AS VARCHAR2(26)) AS "StartDate"
FROM MYSPACE.MYTABLE

Then in SQL Server, you can convert to a datetime like this:

SELECT CAST(CONVERT(DATETIMEOFFSET, StartDate) AS DATETIME)

If you're on SQL Server 2016 or later, you can use AT TIME ZONE and your local timezone to get the adjusted datetime value:

SELECT CAST(CONVERT(DATETIMEOFFSET, StartDate) AT TIME ZONE 'Pacific Standard Time' AS DATETIME)