none
Gen2 to SQL Database Table adding derived column during Copy Activity RRS feed

  • Question

  • How to Add derived column using the copy activity from source Gen2 to target SQL Database Table.

    I come across option to define explicit schema in configuration table but in my case, I want to load all the columns from flat file to the table as is and append 2 derive columns during copy such as pipeline parameter "source name" hardcoded value and Inserted Datetime Stamp "utcnow()". 

    I can not use the data flow, due to the cost restriction. 

    Thanks,

    Sam



    • Edited by Sam cit Wednesday, December 4, 2019 6:42 PM
    Wednesday, December 4, 2019 6:41 PM

All replies

  • Hello Sam and thank you for your question.  Normally I would send you to Data Flow.  Since you are prohibited from doing so, I will do my best to come up with an alternative, as copy activity does not support derived columns.

    I notice that your sink is SQL.  This opens up some options.  If your sink is Azure SQL, then we can use Lookup Activity and Stored Proc activity.  If your sink is non-Azure SQL, then we can only use Lookup Activity.

    My idea is to use a Lookup Activity to get the data from your Gen2 source, and then either:

    • Send the data and utcnow() as parameters to a stored proc for writing in sink

    Or

    • Use the Lookup activity to write a custom "query" with an insert statement to write the values and utcnow() to sink.  This statement should be followed by statement which returns data.  Select 1; would be a dummy example.

    The writing activity can be placed inside a For-Each loop which iterates over the rows output by the reading Lookup activity.

    Wednesday, December 4, 2019 11:59 PM
    Moderator
  • I don't understand the comment about "cost restrictions".

    Cost per hour of Copy Activity, which you are using: Data movement activities: $0.25/DIU-hour.

    Cost per hour of Data Flow Activity: $0.193 per vCore-hour.

    Thursday, December 5, 2019 4:32 AM
  • Sam Cit, since we haven't heard back, we will assume the issue resolved.
    Tuesday, December 10, 2019 5:50 PM
    Moderator
  • Thanks Martin / Mkro for your reply.

    Sorry for late reply, let me test the append variable and confirm.

    @mkro - The purpose is to do the copy activity and there adding audit columns for example inserted date time or source column name. I do not think dataflow is required in my case for adding additional column. I would like to handle that in copy activity itself.

    Regards

    Sam


    Monday, December 16, 2019 3:55 PM
  • Your sink is SQL, so you can take advantage of the Stored Proc in Sink feature (Azure) (Non-Azure).  This allows you to insert your auditing during write.  This may slow down the copy, so you will need to evaluate whether this is suitable for your solution.
    Monday, December 16, 2019 9:03 PM
    Moderator
  • We have not received a response from you.  Are you still facing the issue?  If you found a solution, would you please share it here with the community?  Otherwise, let us know and we will continue to engage with you on the issue.
    Wednesday, December 18, 2019 9:26 PM
    Moderator
  • Since we have still not heard back from you, we will assume you found your own resolution.  If you found a solution, would you please share it here with the community?
    Thursday, December 19, 2019 11:32 PM
    Moderator