CODEWITHSUNDEEP
sql-serverssisssis-2012
karaode
karaode

Reputation: 19

Converting String to Date in SSIS derived column

I have project parameter whose value will be like 'Group_AR_21Sep2021' and I want to fetch this date and put it into one of the column of the table which I am loading from Excel. So I put this in derived column (DT_DBDATE)(SUBSTRING(@[$Project::ARFileName],10,18)) but it is throwing me this error

[Derived Column [2]] Error: Casting expression "(SUBSTRING(@[$Project::ARFileName],10,18))" from data type "DT_WSTR" to data type "DT_DBDATE" failed with error code 0xC00470C2.

Upvotes: 0

Views: 318

Answers (1)

Mark Wojciechowicz
Mark Wojciechowicz

Reputation: 4477

The second parameter to the substring function should be the length of the date: (DT_DBDATE)(SUBSTRING(@[$Project::ARFileName],10,9)) However, this would still fail because SSIS is super fragile when typecasting to date. I would suggest using a script component instead:

1 - Select the project parameter in the read only variables collection

2 - Add a column to the output columns and make it DT_DBDATE, i.e. NewDate

3 - Update the script to do the cast to the new column:

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    Row.NewDate = Convert.ToDateTime(Variables.ARFileName.Substring(9,9));
}

Upvotes: 1

Related Questions