need help in ssis expression

Question

Hi Guys

Need your help in ssis expression. I have csv sales file with various dates . column data is like for example 25 june 2021.

What i am trying to do is convert let say for example 25 june 21 to 2021-06-25 in my csv file and then

use 2021-06-25 to split the file with all data for specific dates i want. let say i want one file which has all data for 2021-06-25

Not sure about my ssis expression. It seems wrong.
PLease can anyone advise.

So I have taken

1. Derived column transformation and then converting 25 June 21 t0 2021-06-25

My expression is
(DT_STR,4,1252)DATEPART("yyyy",(DT_DATE)[Transaction Date]) + "-" + RIGHT("0" + (DT_STR,4,1252)DATEPART("m",(DT_DATE)[Transaction Date]),2) + "-" + RIGHT("0" + (DT_STR,4,1252)DATEPART("d",(DT_DATE)[Transaction Date]),2)

screenshot :-

and then i am using a derived column transformation to get friday date(this expression is correct.no issues)

next i am using conditional split to get all friday dates which i will save it as a csv file.

the expression (DT_STR,4,1252)DATEPART("yyyy",(DT_DATE)[Transaction Date]) + "-" + RIGHT("0" + (DT_STR,4,1252)DATEPART("m",(DT_DATE)[Transaction Date]),2) + "-" + RIGHT("0" + (DT_STR,4,1252)DATEPART("d",(DT_DATE)[Transaction Date]),2)
doesnt seem to work as it is throwing error

Screenshot

I can also see on data flow task their is a run time error which is

The data types "DT_WSTR" and "DT_Date" are incompatible for binary operator "==".

PLease can anyone guide me with the ssis expression to convert 25 june 2021 to 2021-06-25 .
i guess i will then be abke to complete the package.

Regards
Farhan Jamil

Answer 1

Hi @Farhan Jamil ,

I used two Derived Column to convert 25 june 21 to 2021-06-25.

First is to replace 25 june 21 to 25 06 2021.

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(DATE,"Jan","01"),"Feb","02"),"Mar","03"),"Apr","04"),"May","05"),"June","06"),"July","07"),"Aug","08"),"Sep","09"),"Oct","10"),"Nov","11"),"Dec","12")  

And then convert to 2021-06-25 with below expression.

(TRIM(DATE) == "") ? NULL(DT_WSTR,50) : (DT_WSTR,50)("20" + SUBSTRING(DATE,7,4) + "-" + SUBSTRING(DATE,4,2) + "-" + SUBSTRING(DATE,1,2))  

The result is like shown:

Hope it could give you some ideas.

Regards，

Zoe

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October