Azure Data Factory - Parameters event based triggers

Case
My files arrive at various moments during the day and they need to be processed immediately on arrival in the blob storage container. At the moment each file has its own pipeline with its own event based trigger. Is there a more sustainable way where I don't have to create a new pipeline for each new file?

Event based triggers can pass through parameters

Solution
Yes, if your source files have names that can easily be matched to table names then it is very easy and you only need a couple of parameters and a single Copy Data activity. If you cannot match a source file by its name then you might want to add a Lookup activity that can find the match in a configuration table. Or if unexpected files can also arrive in that same blob storage container then you also might want to add the Lookup activity to check whether the file is expected.

The solution uses parameters that can be filled by the event based trigger. With a simple expression you can pass on the filename and folderpath to the pipeline.

1) Pipeline parameters
Start with a simple pipeline that only contains a single Copy Data activity that copies a specific file to a specific SQL Server table. Then add two parameters to this pipeline. One for the filename and one for the folderpath: SourceFileName and SourceFolderPath. The folder path parameter is optional for this basic example and will contain the containername and the foldername combined: "container/folder".

Add 2 pipeline parameters

2) Dataset sourcefile
Go to the Dataset of your source file from the blob storage container. In the Parameters tab add a new string parameters called FileName. As default value you can use the name of the file. Optionally you can add a second string parameter for the folderpath.

Add parameter FileName to dataset

Next step is to use this newly created dataset parameter in the connection to the file. Go to the Connection tab of your dataset and replace the filename with some dynamic content: @dataset().FileName

Add dynamic content for filename

If you want to use the folderpath parameter as well then you can add it to the container textbox and leave the Directory textbox empty (the containername is mandatory, but besides the container name you can also add the folderpath).

3) Dataset stage table
Now repeat the same actions for the dataset that points to your stage table. First add a parameter called TableName in the parameter tab.

Add parameter TableName to dataset

And then replace the Table property with some dynamic content: @dataset().TableName (check the Edit checkbox below the table before adding the expression).

Use dataset parameter to configure the tablename

4) Copy Data activity - Source
Now go back to your pipeline and edit the Source of your Copy Data activity. Notice that you now see a new Dataset property called FileName that points to the parameter of the dataset. This is where you fill the dataset parameter with the value of the pipeline paramter. The value should be set with some dynamic content: @pipeline().parameters.SourceFileName. This is the pipeline parameter created in step 1.

Copy Data activity - Dynamic content in FileName

5) Copy Data activity - Sink
Now repeat the same for the Sink where you now see a new Dataset property called TableName. Here is where you need to match the filename to the table name. In our example the tablename is equal to the filename without the extension. Therefor we replaced the extension with the replace expression: @replace(pipeline().parameters.SourceFileName,'.csv', '')

Copy Data activity - Dynamic content in TableName

6) Edit Trigger
The last step of the solution is passing through the filename and folderpath from the trigger to the pipeline parameters with an triggerbody expression that you also see in Azure Logic App and Power Automate:
Returns the filename of the file that caused the trigger:
@triggerBody().fileName
Returns the folderpath of the file that caused the trigger:
@triggerBody().folderPath

When you edit or create a new trigger in the pipeline then the last step is filling in the variables. By using the above expressions they automatically fill them with information of the file that caused the trigger.

triggerBody() expressions

When two or more file are uploaded then each of them will trigger the same pipeline separately, but all with different values in the pipeline parameters.

7) Testing
After publishing all changes we can upload several files, one at a time or multiple at once, to test the new trigger pipeline. The last two rows indicate that the files where uploaded at the same time, but both triggered the pipeline separately. You can check the parameters by clicking on [@].

Monitoring the pipeline runs

Conclusion
In this post you learned how to use the triggerBody() expression from the event based triggers. You can pass on its value to the Pipeline parameters and then pass on those pipeline parameters to the dataset parameters.

With this very basic construction you can handle as many files as you like. If you do not need to process them as once then we suggest to use the foreach loop construction instead.

Power Apps Snack: Don't repeat yourself

Case
I have some pieces of code in my Power App that I use for several buttons, but I don't want to create multiple copies of that code. Is there a way to create a method or function with custom code that I can call from various buttons?

Power Apps and custom functions

Solution
No, Power Apps does not support methods or functions like real programming languages. However you can use the SELECT function. This function allows you to execute code from other objects on your screen, but only objects that have an OnSelect event.

Example 1: basics
For this example add two buttons to the screen: Button1 and Button2. In the OnSelect of Button1 add a simple Notify expression:

Notify(
    "Hello",
    NotificationType.Success,
    1000
)

Notify in OnSelect of Button1

In the OnSelect of Button2 add the Select expression:

Select(Button1)

Select in OnSelect of Button2

The Result
Now hit the play button to see the preview of your app. Then click both buttons and see the Notify showing for both buttons. You can even make Button1 invisible if you don't want to execute that code by it self, but only via other buttons.

Testing simple Select solution

Example 2: parameters
By adding variables to the game you can even have some parameters for your new 'function'. Now change the OnSelect code of Button2 to:

UpdateContext({myParam: "Joost"});
Select(Button1)

And then add a third (and optional a fourth) button with the following OnSelect code:

UpdateContext({myParam: "Mark"});
Select(Button1)

Then change the OnSelect code of Button1 to:

Notify(
    "Hello " & myParam,
    NotificationType.Success,
    1000
)

And make Button1 invisible by setting the Visible property to false.

The Result
Now hit the play button again to see the preview of your changes. Click both visible buttons and see the Notify that now shows a different name for both buttons.

Testing Select with 'parameter'

Conclusion
In this post you learned how NOT to repeat yourself with the Select function in Power Apps. Perhaps not the same features as in JavaScript, C# or any other language, but very useful to keep your code more clean. By hiding the buttons and giving them a descriptive, function-like name they could act like real programming functions.

An other great trick is to use the WITH function which is explained in an other post.

Databases in DevOps - Publishing profile

Case
I can’t release my database project due an error “data loss could occur”

error code

Updating database (Failed)
*** Could not deploy package.
Warning SQL72015: The column [dbo].[Table1].[ColumnToRemove] is being dropped, data loss could occur. 

This happens due the fact that my source table changed/removed a column that’s still available in the target table.

Solution
When deploying a database project, you can add an publish file with this deployment. In this file (it’s a xml file) you can set/adjust certain settings
First, we setup visual studio and secondly, we adjust je build pipeline

1) Visual Studio
Here we are going to create the publish file

• Right click on the solution and click “Publish”
• Then a new window pop-up, go to “Advanced”
• Uncheck - 'Block incremental deployment if data loss might occur’ checkbox.

Publish settings - general

• Go to the second tab and check - 'DROP objects in target but not in project' and click “ok”

Publish settings - drop

• Then select “Create profile” and a new a ‘***.publish.xml’ will be added to the solution

Add publish file

• The next step is, is to add the publish file to source control “add ignored file to source control”

Add to source control

2) DevOps 
First, we edit the build pipeline

• Go to Azure DevOps -> Pipelines -> Pipelines
• Then edit your pipeline (please see the blog of Joost for creating a pipeline)
• Go to the task “Copy files” and add to ‘contents’ “**\*.publish.xml” 

Build pipeline

Last step, is to add the xml file to the release pipeline

• Go to pipelines -> release
• Then edit your pipeline and add the publish file

Release pipeline

Summary
In this post you learned how to add a Publish Profile file. In this file you can change the publish settings. In this example we did want to make it possible to truncate tables when a column has been removed.
But it can also be used to disable the deployment of security-related objects to our database, like:

• ExcludeUsers
• ExcludeLogins
• ExcludeDatabaseRoles