Transformations using copy data activity

Question

Please find the below scenario.

As soon as business user uploads the excel file in storage account, ADF captures it and loads into Azure SQL database using copy data activity. This we have set up using event based triggers.

This excel file has 2 columns Costcenter and email id. But we need to add the leading zeroes to each costcenter to make it 10 digits, before loading into Azure Sql db.

Can we do using the same copy data activity with out involving mapping data flows in ADF.

Answer 1

Hi Keerthika,

1. Access your pipeline by heading over to the ADF interface and open up the pipeline that contains your Copy Data activity.
2. Navigate to the 'Mapping' Section: Inside the Copy Data activity, you’ll find a tab labeled "Mapping." Click on this to view the current setup of how your source columns correspond to your destination columns.
3. Locate the “Costcenter” column in the source columns list. Instead of a straightforward column-to-column mapping, we’re going to apply a transformation right here.
4. Craft Your Expression: To ensure each “Costcenter” value has a total of 10 digits, we'll add leading zeroes where necessary. Use the following expression as a guide:

right(concat('0000000000', Costcenter), 10)

Here’s the breakdown: concat('0000000000', Costcenter) adds a series of zeroes in front of your “Costcenter” value. Then, right(..., 10) ensures that only the last 10 characters are taken, effectively giving you a 10-digit “Costcenter,” complete with leading zeroes.

Now that you've set up your transformation, do a quick check to ensure everything looks good, and hit 'Save.'

To make sure everything is working as expected, run your pipeline. You can do this either by uploading a new file (if your pipeline is triggered by event) or manually triggering it.

And there you have it! By following these steps, you can add leading zeroes to your “Costcenter” values right within the Copy Data activity, eliminating the need for a separate Mapping Data Flow. Keep in mind, though, for more intricate data manipulations, Mapping Data Flows in ADF offer a robust set of tools and functionalities.

Answer 2

Hi Keerthika ,

As per my understanding you want to transform the data using copy activity in ADF where you want to add zeroes at the end of the column data without the need to use Mapping dataflow. Please let me know if that is not the requirement.

You would need couple of more activities to achieve this - lookup activity to get the data from the source, foreach to iterate through each of the costcentreid and then inside foreach , set variable activity to concat the number with zero and then use script activity to load the transformed data into the sql table.

Watch out this video on How to add zero padding to the numbers using azure data factory pipelines where the logic that you need to use within foreach is described.

For any query, kindly comment below. Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou