1

My source is CSV contains enddate & startdate column datype string. I need to change those date columns in destination table format.

source -

enter image description here

Destination :

SALES_ORGANIZATION  STARTDATE   ENDDATE EXTRACT_MNTS
AE93-AE-CS  2020-01-01  2020-12-31  4
AE93-AE-CS  2020-01-01  2020-12-31  4
AE93-AE-CS  2020-01-01  2020-12-31  4
AE93-AE-CS  2020-01-01  2020-12-31  4

I am using data flow : enter image description here END Enddate is not coming as excepted : enter image description here

What is the way I already check todate(ENDDATE,'YYYY-MM-DD') this is giving NULL.

Eric Brandt
  • 7,886
  • 3
  • 18
  • 35
anuj
  • 124
  • 2
  • 13
  • Have you considered a staging table on the database where you dump the CSV contents as they are and THEN do the manipulation? ELT is far easier (and generally better performing) than ETL. – Anthony Hancock Jul 07 '21 at 14:58
  • Hi you are saying to load csv in one stg table and that stg table I can choose as a source in data flow ? Am I understanding correcting? – anuj Jul 07 '21 at 15:19
  • Well ideally, you would then write a stored procedure to load from the stg table to the dst table and call that sproc in the data flow. The least amount of transformation you can do outside of the DB the better. The sproc method also means you don't have to modify your azure data factory to change logic, you just update the sproc. – Anthony Hancock Jul 07 '21 at 15:34
  • I am stuck while copying the data from blob to stg table, I have 5 date columns out of which one date column have NULL while copying to sink synpase with datatype date it is throwing a error for datatype conversion failed NUll would not allow in date column. The same issue I am facing in data flow also. – anuj Jul 07 '21 at 15:38
  • 1
    don't use datatypes in stage table, make everything string (NVARCHAR) – Anthony Hancock Jul 07 '21 at 15:45
  • Also, if it's saying NULL isn't allowed, then just don't make the column NOT NULL. – Anthony Hancock Jul 07 '21 at 15:45
  • Hi Anthony it is working for me I have created a table with nvarchar & refred that stg table as a source in mine dataflow after that alter table upsert then sink as a core table. It is working without using derived column transformation. Still My question is not answered how we can achieve it directly from data flow without stg table concept. – anuj Jul 07 '21 at 16:08
  • honestly, you just shouldn't do it in the data flow. It's going to be a lot slower both in processing time and development (see how easy it was to do the stg table vs the data flow). – Anthony Hancock Jul 07 '21 at 16:34
  • One more question flow -----copy activity -- then Mapping data flow (--- stg table my source -- upsert ok key column -- sink on the core table.) so dataflow I am using for upsert it is fine or store procedure way I have to go ?? – anuj Jul 07 '21 at 16:37
  • Stored procedure has a lot of benefits. You don't have to go into your Azure project if you need to mod the code, it has proc caching, more accessible to other people, etc. You CAN do whatever you want, but sproc usually has more value. – Anthony Hancock Jul 07 '21 at 16:39
  • Hi Anthony yup from starting Sp / CTAS in synpase we can write but just to reduce the coding efforts I think for a mapping data flow. I am not sure if still we need to go by old sp traditional way they I am in dilemma what's the use of data flow in adf? – anuj Jul 07 '21 at 16:53
  • 1
    ADF and it's precursor SSIS are very good for workflow organization and getting data (as is) from point A to point B. They are not very good for manipulating that data in flight though, so that's why you tend to leave that to the DB – Anthony Hancock Jul 07 '21 at 17:36
  • 1
    Hi Anthony yes that correct I have implemented the ELT logic & it is working fine. Thanks for your quick reply. Your inputs is valuable for me. – anuj Jul 08 '21 at 14:25

1 Answers1

1

Use toDate() condition inside iifNull() condition and it will work fine. Also, make sure that MM in date format should be in upper case.

Eg: iifNull(toDate(start, 'MM/dd/yyyy'), toDate(start, 'dd-MM-yyyy'))

Find the snippets below.

Source data preview: enter image description here

Derived column data preview: enter image description here

Utkarsh Pal
  • 4,079
  • 1
  • 5
  • 14