CODEWITHSUNDEEP
azure-data-factory
fchukoskie
fchukoskie

Reputation: 346

Substring of column name in Copy Activity in ADF v2

Is there a way in the V2 Copy Activity to operate upon one of the input columns (of type string) with an expression? Before I load rows to the destination, I need to limit the number of characters in the column.

My hope was to simply switch from something like this:

"ColumnMappings": "inColumn: outColumn"

to something like this:

"ColumnMappings": "@substring(inColumn, 1, 300): outColumn"

If anyone can point me to where I can read-up on where & when string expressions can be used, I could use the guidance.

Upvotes: 2

Views: 7174

Answers (2)

Simon Zeinstra
Simon Zeinstra

Reputation: 815

When you don't have a SQL Source, but your destination is a SQL sink, you can use a Stored Procedure to insert your data into the final table. That way, you can define these kinds of transformations in the stored procedure. I don't think the Data Factory can handle these kinds of activities, it is more intended as an orchestrator.

Have a look here: https://learn.microsoft.com/en-us/azure/data-factory/connector-sql-server#invoke-stored-procedure-from-sql-sink

Upvotes: 2

Martin Esteban Zurita
Martin Esteban Zurita

Reputation: 3209

This is the official documentation on expressions and functions: https://learn.microsoft.com/en-us/azure/data-factory/control-flow-expression-language-functions

And this is the documentation on mappings: https://learn.microsoft.com/en-us/azure/data-factory/copy-activity-schema-and-type-mapping

Also remember that if you are using a defined query in the copy activity, you can use sql functions like CAST([fieldName] as varchar(300)) to limit the amount of characters on a particular field.

Hope this helped!

Upvotes: 6

Related Questions