Search code examples
sql-serverssisssis-2012

Extract Date from a string using Derived Column in SSIS

I have a column called FileName and I wanted extract the date from the column as a Date column using Derived Column in SSIS. The FileName Data type is nvarchar(260)

FileName

M:\Mapping\Workforce_Planning\ABC\ABClrmp.full.2016-01-07.csv

Desired Result

2016-01-07

I have been using SQL task but I prefer to use Derived Column.

Update [dbo].[GET]
SET [Date] = CONVERT(date, REPLACE(SUBSTRING([FileName], LEN([FileName])-CHARINDEX('\',REVERSE([FileName]),0)+2, LEN([FileName])-(LEN([FileName])- CHARINDEX('\',REVERSE([FileName]),0))),'.csv',''), 121)
Solution

  • Just add a derived column with the following expression

    LEFT(RIGHT([Filename],14),10)

    Read more about Derived Column Transformation in this MSDN article

    Test

    i tested this expression on the following path M:\Mapping\Workforce_Planning\ABC\ABClrmp.full.2016-01-07.csv and it gives the following result 2016-01-07