SSIS imports datetime column in SQL Server 2008

Question

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

Everything works fine except for one field containing a datatime .

The contents of the imported file contain the date and time in the format

 2012-08-08T13:31:28.170 

File Connector Settings for a PSV file is a database label with precision [DT_DBTIMESTAMP2]

The destination column in SQL Server is a datetime data type.

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

 2012-08-08 00:00:00.000 

You will notice that mins / secs were not imported.

I should use the wrong datetime formats, but seemed to try all combinations without success.

Can someone point me in the right direction?

Answer 1

I had a similar situation where the problem was at my source and not at the destination.

I suggest you check this field with SourceComponent by right-clicking on it, choosing Show Advanced Editor → imput and Output Properties → Expand Output Columns → Select your column and change the data type propper (usually [DT_DBTIMESTAMP] works fine for me). Also for testing, do the same on "Output columns"

Answer 2

TL; DR

Use DT_DBTIMESTAMP as your type and set fastParse to true

Customization

I created a CSV with the following lines. Since SQL Server has a precision of 0.003 ms for datetime, this would ensure that any rounding problems are displayed on the 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 a 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] 

Then I created a connection manager named dt_dbtimestamp and defined one column in the Advanced section named EventDate and the database timestamp [DT_DBTIMESTAMP] data type database timestamp [DT_DBTIMESTAMP]

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

Then I right-clicked on the file with the flat file and selected Show Advanced Editor . On the Input and Output tab, I expanded the Flat File Source output file control and expanded the output columns again, and then selected EventDate. In custom properties, I changed the default value for FastParse from False to True

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

I used the OLE DB assignment and connected it 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