SSIS - Issue in exporting date time stamp (Format:

Question

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.

Answer 1

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.

Source Query

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);

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 

Package

Dread simple package

Biml reproduction

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>