CODEWITHSUNDEEP
azureazure-data-factory
jnoguerm
jnoguerm

Reputation: 147

How transform a column befory copy?

I would like to copy a table from sourceA to sourceB, but before I want to transform column values. How could I do it? I've tried to do it in the mapping trying to add dynamic content but I think I'm not doing it correctly.

Thanks!

Upvotes: 1

Views: 1449

Answers (4)

Mark Kromer MSFT
Mark Kromer MSFT

Reputation: 3838

Use Mapping Data Flows in ADF: https://learn.microsoft.com/en-us/azure/data-factory/data-flow-derived-column

Upvotes: 0

Prashant Mishra
Prashant Mishra

Reputation: 26

Before I can answer your question, it will be relevant to know the volume and source of data. However, there could be three possible solutions for this problem.

1. Stored Procedure Activity in Azure Data Factory

  • Use Copy Activity in Azure Data Factory to bring data to SQL(Staging tables), but if your source is SQL already then you can skip this step.
  • Write a stored procedure to include all the transformations that you need.
  • Use Stored procedure activity to make all the required transformations and insert the data to final table.

2. Use SSIS package in Azure Data Factory

  • You don't need to bring data to staging, you can create SSIS package using SQL Server Data Tools on your local machine. Add source, transformations and destinations

  • Add this SSIS package in Azure Data Factory, if it's mandatory for you to have Azure Data Factory otherwise you can just schedule your SSIS package using SQL Job Agent.

3. Use Data Flow with Azure Data Factory V2.0

  • Data Flow is in preview mode right now so you can not use this solution for any production ready solution.

  • While creating Azure Data Factory, it will ask you the option to choose from Data Factory v1, Data Factory V2 or Data Flow with Data Factory V2. So, you need to choose third option

  • Now, you can perform most of your transformations here that you could perform on SSIS package. It's easy to use, all drag and drop facilities for various transformations between source and destination. And this is the best solution even if the data volume is very high.

Hope this will be helpful for you. All the best. Cheers!

Upvotes: 0

silent
silent

Reputation: 16128

Your use case sounds like you could use the new Data Flow feature (currently in preview): https://learn.microsoft.com/en-us/azure/data-factory/data-flow-create

Upvotes: 2

Jay Gong
Jay Gong

Reputation: 23782

jnoguerm,please still adopt the stored procedure idea which I mentioned in the previous case:How to copy data in Azure Data Factory depending on the values?.

You could copy the data from source table to the temporary table.And do the transform business and insert operations in the stored procedure.In this case: Azure Data Factory mapping 2 columns in one column,the questioner wants to merge two columns into one column(change,merge,calculate,update,reduce etc.),you could refer to the details in that page.

Upvotes: 1

Related Questions