Timestamp to Bigint (MSSQL) vs. DT_BYTES to DT_I8 (SSIS)

Question

I'm having issues doing proper data conversion in SSIS when dealing with the "timestamp" data type from SQL Server.

I have a table that im staging from using SSIS. In the source-select in the top of my dataflow task (DFT), it looks something like this:

SELECT
     [SourceColumn1Timestamp]       = [SourceColum1Timestamp]
   , [SourceColumn2]               = [SourceColumn2]
   , [SourceColumn3]               = [SourceColumn3]
   , [SourceColumn4]               = [SourceColumn4]

FROM [dbo].[Table1]

I would like to convert the [SourceColumn1Timestamp] to Bigint, but inside SSIS using a data conversion component in the dataflow task. Like this:

So the input column "timestamp" which is the "DT_BYTES" SSIS data type gets converted into DT_I8.

However the results are completely different from what I expected. And different from the results I would get if i did the conversion in SQL, adjusting the source-select in the DFT to this:

SELECT
     [SourceColum1Timestamp]        = [SourceColum1Timestamp]
   , [SourceColumn2]                = [SourceColumn2]
   , [SourceColumn3]                = [SourceColumn3]
   , [SourceColumn4]                = [SourceColumn4]
   , [SourceColum1Timestamp_BIGINT] = CONVERT(BIGINT, [SourceColum1Timestamp]) -- I would like to avoid this
FROM [dbo].[Table1]

How come?

Here one row showing the difference between SQL conversion and SSIS: SQL:

SSIS:

Answer 1

If you want to get the same value in SSIS as T-SQL, you'll need to reverse the byte sequence of the binary value in your transformation. That will interpret the binary value as little-endian sequence (least significant byte first) so the value will be converted to a 64-bit signed integer as expected.

EDIT:

I don't know if there's a better way in SSIS but you could reverse the timestamp byte array and convert to a 64-bit integer in a C# script transformation. Here's a function to do that instead of your existing transformation:

Int64 convertTimestampToInt64(byte[] timestampBytes)
{
    Array.Reverse(timestampBytes, 0, timestampBytes.Length);
    var timestampInt64 = BitConverter.ToInt64(timestampBytes, 0);
    return timestampInt64;
}