Data Type( Source and Target)

Rohit Kulkarni 441 Reputation points
2021-02-22T15:50:59.58+00:00

Hello Team,

I am transferring the data from Source to target .There are more than 200 fields. The data type from source to Target are not matching.
I don't want to hardcode the datatype Because if any function got changed then data type will be changed in Target fields .So i don't want to go back and change in the Pipeline.

Is there any built in function in azure data factory pipeline where i can use that function so that in future if any data type gets changes in Target. Dynamically it will get changes in the pipeline.

Please let me know what type of Built function i can use.

Regards
Rk

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,906 questions
{count} votes

2 answers

Sort by: Most helpful
  1. HimanshuSinha-msft 19,386 Reputation points Microsoft Employee
    2021-02-22T23:33:58.403+00:00

    Hello @Rohit Kulkarni ,
    Thanks for the ask and using the Microsoft Q&A platform .

    I am pretty confident that there is no function at this time in Azure data factory which can help here . When we create a pipeline it reads the top records from the source and determines the data types and if there is a different data type on the sink side than what is expected the pipeline will fail . Moreover on a side note updating the data types on the sink can corrupt the data . Lets say that yesterday the pipeline runs fine and ingest the data of type varchar(8) and the record is "Himanshu" , ingestation works fine . Today by some means the data coming of different type is say varchar(5) and you tend to update the table to varchar(5) . The ingestion which you did yesterday "Himanshu" will now be updated to "Himan" . Please share more insight on the design , may be we can help .
    One thing which you can try is to have all the fields on the sink to be varchar(X) , this may work out for you .

    Please do let me know how it goes .
    Thanks
    Himanshu
    Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

    1 person found this answer helpful.
    0 comments No comments

  2. Rohit Kulkarni 441 Reputation points
    2021-02-23T04:43:21.413+00:00

    In source I have created a stored procedure and converted the data type so it will match with the Target DB.

    Example

    CAST('' AS NVARCHAR(40)) AS [FIELD1]
    CAST([Owned] AS INT) AS [FIELD2]
    CAST('' as datetime) AS [FIELD3]

    Regards
    RK

    0 comments No comments