How to transform string date from a text file to SQL Server 2005?Posted: June 18, 2008
You can transform your string date through a Derived Column component to a new derived column with the Expression like: (Assume your date is in YYYYMMDD format)
(DT_DBTIMESTAMP)(SUBSTRING(mydate,5,2) + "/" + SUBSTRING(mydate,7,2) + "/" + SUBSTRING(mydate,1,4))
So, the whole process is:
(Under Data Flow Tab)
1. Drag a Flat File Source ;
2. Drag a Derived column and use DT_DBTIMESTAMP transformation with SUBSTRING manipulations;
3.map the derived column with a new table in SQL Server Destination.