Hello Jeff,

Sorry for that and got your point, I am using - Microsoft SQL Server 2017. Please fine the sample data in SQL format.

SELECT '-1010-----' AS Field1,
...

• This reply was modified 1 year, 11 months ago by  sabarishbabu.
• This reply was modified 1 year, 11 months ago by  sabarishbabu.

Thank you Scott for you solution !!

Thank You Phil - Sorry for the late reply - Input Data Type is varchar and the  max precision is 4.

Thanks Thom!!

I am trying to convert in the DFT task, however I could see the below default data conversion, I am not sure how to convert from ISO format.

DT_DBDATE

yyyy-mm-dd

DT_FILETIME

yyyy-mm-dd hh:mm:ss:fff

DT_DBTIME

hh:mm:ss

DT_DBTIME2

hh:mm:ss[.fffffff]

DT_DBTIMESTAMP

yyyy-mm-dd...

Thanks Phil !! If I add new constraints it will blocking some existing system. so I can't create any constraint on this table.

In addition, the Company table having multiple columns...

Thank you Phill !! Its working as expected I just added trim function 🙂

TRIM(TOKEN(REPLACE(SourceRecord,"\"\"","\" \""),"\"",1))}

Thanks for your quick reply !! Yes the  quotation marks are there in the empty field too, even its not working for if any empty column value.

Source Data

"A","","","","A,B,C,D"

"X","X1,X1","20","+","X,B,C,D"

"Y","Y1,B1","100","+","Y,B,C,D"

"Z","A1","","","Z"

• This reply was modified 4 years, 5 months ago by  sabarishbabu.

Thanks you Phill !!

Your solution is working as expected, however any of the column value has empty, I did not get the proper value in corresponding output columns.

Source Column/ OutPut...

• This reply was modified 4 years, 5 months ago by  sabarishbabu.

Thank again Jeff!!

STUFF() deletes a part of a string and then inserts another part into the string, starting at a specified position.

Step 1 : Add T between Date and Time...

Thank you Jeff Moden - STUFF one :). however thank you both 🙂

Thank you !! Working as expected

Thanks for your suggestions !!

Added additional note on my post, Hashkey values are from source system, I am not generated hashkey while loading datamart.

I am using  hashkey for UPSERT records...