SSIS - 导出日期时间戳问题(格式:yyyy-mm-dd 00:00:00:000 [英] SSIS - Issue in exporting date time stamp (Format: yyyy-mm-dd 00:00:00:000

问题描述

我有一个 SSIS 将自定义表中的导出数据打包，其中包含日期时间戳列 (yyyy-mm-dd 00:00:00:000).创建文本文件时，它会删除最后一个0".我将数据类型更改为数据库时间戳 [DT_DBTIMESTAMP] IN ssis 但它不起作用.

I have an SSIS package the export data from a custom table which contains a datetime stamp column (yyyy-mm-dd 00:00:00:000). When the text file is creating it is dropping the last '0'. I change the data type to database timestamp [DT_DBTIMESTAMP] IN ssis but it doesnt work.

推荐答案

对我来说效果很好，你在做什么?如果将数据类型作为任何非字符串类型引入，则可以修剪所有尾随零，因为将 100.00 写入 100.000000000 与 100 的值相同.如果尾随零很重要，则必须将其强制转换为字符串类型以保留这些值.

Works fine for me, what are you doing? If you are bringing in the data type as anything that isn't string, then all the trailing zeros can be trimmed because writing 100.00 is the same value as 100.000000000 as 100. If the trailing zeros are important, then you must cast it to a string type to preserve those values.

我有一行作为日期时间数据类型和使用 ISO 格式的字符串发送到数据流

I have one row which I send to the data flow as both a datetime data type as well as a string using the ISO format

SELECT
    D.Val AS ValueAsDateTime
,   CONVERT(char(24), D.Val, 121) AS ValueAsString
FROM
( 
    VALUES 
    ( CAST('2015-03-17T23:59:59.997' AS datetime)) 
) D (Val);

输出

我定义了一个分别使用 DT_DBTIMESTAMP 和 DT_STR 作为类型的 CSV.这是存储在我的文件中的内容

Output

I defined a CSV which uses DT_DBTIMESTAMP and DT_STR respectively as the types. This is what was stored to my file

ValueAsDateTime,ValueAsString
2015-03-17 23:59:59.997000000,2015-03-17 23:59:59.997 

包装

可怕的简单包

如果你安装了BIDS Helper，下面的Biml会生成我的复制包.

If you have BIDS Helper installed, the following Biml will generate my reproduction package.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <!-- Create a basic flat file source definition -->
    <FileFormats>
        <FlatFileFormat
            Name="FFF"
            CodePage="1252"
            RowDelimiter="CRLF"
            IsUnicode="false"
            FlatFileType="Delimited"
            ColumnNamesInFirstDataRow="true"
        >
            <Columns>
                <Column
                    Name="ValueAsDateTime"
                    DataType="DateTime"
                    Delimiter=","
                    ColumnType="Delimited"
                />
                <Column
                    Name="ValueAsString"
                    DataType="AnsiString"
                    Delimiter="CRLF"
                    InputLength="20"
                    MaximumWidth="20"
                    Length="20"
                    CodePage="1252"
                    ColumnType="Delimited"
                    />
            </Columns>
        </FlatFileFormat>
    </FileFormats>

    <!-- Create a connection that uses the flat file format defined above-->
    <Connections>
        <FlatFileConnection
            Name="CM_FF"
            FileFormat="FFF"
            FilePath="C:\ssisdata\29520836.txt"
            DelayValidation="true"
        />
        <OleDbConnection
            Name="CM_OLE"
            ConnectionString="Data Source=localhost\dev2014;Initial Catalog=tempdb;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;"
        />

    </Connections>

    <!-- Create a package to illustrate how to apply an expression on the Connection Manager -->
    <Packages>
        <Package
            Name="so_29520836"
            ConstraintMode="Linear"
        >

            <!-- Create a single variable that points to the current file -->
            <Variables>
                <Variable DataType="String" Name="QuerySource">SELECT D.Val As ValueAsDateTime, CONVERT(char(24), D.Val, 121) AS ValueAsString FROM (VALUES(CAST('2015-03-17T23:59:59.997' AS datetime)))D(Val);</Variable>
            </Variables>

            <!-- Add a foreach file enumerator. Use the above -->
            <Tasks>

                <Dataflow Name="DFT Import file" DelayValidation="true">
                    <Transformations>
                        <OleDbSource ConnectionName="CM_OLE" Name="OLE_SRC Query">
                            <VariableInput VariableName="User.QuerySource" />
                        </OleDbSource>
                        <FlatFileDestination ConnectionName="CM_FF" Name="FF_DST"></FlatFileDestination>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>

这篇关于SSIS - 导出日期时间戳问题(格式:yyyy-mm-dd 00:00:00:000的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！