SSIS importing datetime column into SQL Server 2008

Question

I am trying to import a PSV file into SQL Server 2008 using SSIS.

All is working fine apart from one field that contains a datatime.

The contents of the file being imported contains datetime in the format of

2012-08-08T13:31:28.170

The file connector settings, for the PSV file are database timestamp with precision [DT_DBTIMESTAMP2]

The destination column in SQL Server has a datetime datatype.

The result of the package / contents of the SQL table is a datetime import:

2012-08-08 00:00:00.000

You will notice that the mins/secs have not been imported.

I must be using the wrong datetime formats, but seemed to have tried all combinations with no success.

Can anyone point me in the right direction ?

Answer 1

tl;dr

Use DT_DBTIMESTAMP as your type and set fastParse to true

Set up

I created a CSV with the following rows. Since SQL Server only has a precision of .003ms for datetime, this would ensure any rounding issues would surface

2012-08-08T13:31:28.170
2012-08-08T13:31:28.171
2012-08-08T13:31:28.172
2012-08-08T13:31:28.173

I created my target table

CREATE TABLE [dbo].[datetime2Demo]
(
    [EventDate] [datetime2](7) NOT NULL
,   [insert_date] [datetime2](7) NOT NULL DEFAULT(current_timestamp)
,   [string_type] [varchar](50) NULL
) ON [PRIMARY]

I then created a connection manager, named dt_dbtimestamp and defined one column under Advanced with a name of EventDate and a data type of database timestamp [DT_DBTIMESTAMP]

In my data flow, I added a flat file source and used the above connection manager.

I then right clicked on the Flat File Source and selected Show Advanced Editor. On "Input and Ouput Properties" tab, I expanded my Flat File Source Output control and again expanded the Output Columns and then selected my EventDate. Under Custom Properties, I changed the default value for FastParse from False to True

I had a derived column that added the string_type value (DT_STR,20,1252)"DT_DBTIMESTAMP" so I could keep track of what worked and didn't.

I used an OLE DB destination and wired it up to the table I created.

Results

SELECT EventDate, string_type FROM dbo.datetime2Demo

EventDate                      string_type
2012-08-08 13:31:28.0000000    DT_DBTIMESTAMP2
2012-08-08 13:31:28.0000000    DT_DBTIMESTAMP2
2012-08-08 13:31:28.0000000    DT_DBTIMESTAMP2
2012-08-08 13:31:28.0000000    DT_DBTIMESTAMP2
2012-08-08 13:31:28.0000000    DT_DATE
2012-08-08 13:31:28.0000000    DT_DATE
2012-08-08 13:31:28.0000000    DT_DATE
2012-08-08 13:31:28.0000000    DT_DATE
2012-08-08 00:00:00.0000000    DT_DBDATE
2012-08-08 00:00:00.0000000    DT_DBDATE
2012-08-08 00:00:00.0000000    DT_DBDATE
2012-08-08 00:00:00.0000000    DT_DBDATE
2012-08-10 13:31:28.0000000    DT_DBTIME2
2012-08-10 13:31:28.0000000    DT_DBTIME2
2012-08-10 13:31:28.0000000    DT_DBTIME2
2012-08-10 13:31:28.0000000    DT_DBTIME2
2012-08-08 13:31:28.1700000    DT_DBTIMESTAMP
2012-08-08 13:31:28.1710000    DT_DBTIMESTAMP
2012-08-08 13:31:28.1720000    DT_DBTIMESTAMP
2012-08-08 13:31:28.1730000    DT_DBTIMESTAMP

Answer 2

I had a similar situation once where the problem was on my source, not on the destination.

I suggest you to check the field on the SourceComponent by right clicking it, selecting Show Advanced editor -> imput and Output properties -> Expand "Output columns" -> Slect your column and change to the propper data type (usually [DT_DBTIMESTAMP] works fine for me). Also for the sake of testing, do the same on "Output columns"