How to convert a timestamp in string format to datetime data type within a package?

2023-03-11 06:11 问答作者：

Could someone guide me on how to convert 开发者_运维问答MM/DD/YYYY HH:MM that comes from a flat file source as a string to a datetime to a ole db destination using the derived column.

This is my flow.

Flatfile -> Data Coversion -> Derived Column

There are couple of ways to achieve this. You can use the Derived Column transformation to convert the value or you can declare the flat file column on the Flat File Connection as column of data type database timestamp [DT_DBTIMESTAMP]. Steps 1 - 8 describes the first option and step 9 describes the second option.

Step-by-step process:

This example uses the flat file named Source.txt shown in screenshot #1.
Flat File Connection is configured as shown in screenshots #2 and #3.
Data Flow Task is configured as shown in Screenshot #4.
Flat File Source is configured as shown in Screenshot #5.
Derived Transformation task is configured as shown in screenshot #6 using the type cast DT_DBTIMESTAMP to convert the string value to date time value.
OLE DB Destination is configured as shown in screenshot #7.
Screenshot #8 displays the sample package execution.
Screenshot #9 displays the data in the SQL table after the package execution.
another easier way to do this to declare the column that contains the date time value as data type database timestamp [DT_DBTIMESTAMP] as shown in screenshot #10. This way you don't need a Derived Column Transformation and the Data Flow Task will be as shown in screenshot #11. You can directly map the Flat File columns to the OLE DB destination columns.

Hope that helps.

Screenshot #1: