question

FarhanJamil-5363 avatar image
0 Votes"
FarhanJamil-5363 asked ZoeHui-MSFT edited

need help in ssis expression

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 :-


109899-image.png



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


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

109971-image.png

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

109926-image.png

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


sql-server-integration-services
image.png (10.9 KiB)
image.png (8.8 KiB)
image.png (2.7 KiB)
image.png (13.6 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered ZoeHui-MSFT edited

Hi @FarhanJamil-5363,

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:

109996-0629.png

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


0629.png (20.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.