对Bigint (MSSQL)和DT_BYTES到DT_I8 (SSIS)的时间戳

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

在处理来自SQL Server的“时间戳”数据类型时，我在SSIS中进行正确的数据转换时遇到了一些问题。

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:

我有一个表，我通过使用SSIS进行分段。在dataflow任务(DFT)顶部的source-select中，它看起来是这样的:

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:

我想将[SourceColumn1Timestamp]转换为Bigint，但是在SSIS中使用dataflow任务中的数据转换组件。是这样的:

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

因此输入列“时间戳”即“DT_BYTES”SSIS数据类型转换为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:

然而，结果与我预期的完全不同。与我在SQL中进行转换时得到的结果不同，我将DFT中的源代码选择调整为:

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:

这里有一行显示了SQL转换和SSIS: SQL之间的区别:

SSIS:

SSIS:

2 个解决方案

#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.

如果您想在SSIS中获得与T-SQL相同的值，您需要在转换中反转二进制值的字节序列。这将把二进制值解释为little-endian序列(首先是最不重要的字节)，因此该值将按照预期被转换为64位有符号整数。

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:

我不知道在SSIS中是否有更好的方法，但您可以逆转时间戳字节数组，并在c#脚本转换中转换为64位整数。这里有一个函数来代替你现有的变换:

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

#2

Based on this microsoft article, I think that timestamp can be implicitly converted into BigInt

基于这篇microsoft文章，我认为时间戳可以隐式地转换为BigInt