Sink: Syntax for multi line post sql scripts

jk 11 Reputation points
2022-03-02T01:14:33.297+00:00

How do I fire off a multi line SQL script using the "post sql scripts" feature in a sink?

The (i) description of the feature explicitly states that it allows multi line scripts but doesn't show how

The expression builder shows an error if I have a multi line expression. the doco only shows very simplistic single line scripts too like turning identity insert on/off

I want to run an update across all rows that were touched by the dataflow. eg

update {$target}
set {$col1} = {$col2}
WHERE job_id = '{$job_id}'

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,456 questions
0 comments No comments
{count} vote

2 answers

Sort by: Most helpful
  1. HimanshuSinha-msft 19,381 Reputation points Microsoft Employee
    2022-03-05T02:07:36.797+00:00

    Hello @jk ,
    Thanks for the question and using MS Q&A platform.

    As we understand the ask here is to use the "Post SQL scripts " feature on the sink side and pass the SQL scripts dynamically , please do let us know if its not accurate.

    At this time I do not think we have that feature , as when you click on the dynamic content -> input schema ( lists of scripts or custom sctipts ) , it
    does not show up the input schema . You can pass static sql content and it should run just fine .

    Just curious , how are you getting the {$target},{$col1} &{$col2].?

    Thanks
    Himanshu


    • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
    • Want a reminder to come back and check responses? Here is how to subscribe to a notification
      • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators
    0 comments No comments

  2. jk 21 Reputation points
    2022-03-07T03:42:28.903+00:00

    i gave up and put it all on a single line. I found another interesting bug where it deletes your entire pipeline on checkin into git if you have /comments/ in your post sql script. good times

    they're input parameters into the data flow

    I guess 'not supported' is the answer right now