0845 643 64 63

Azure

ADF: Where have my connections gone?

If you’ve been doing any development work in ADF this week you might have noticed that “Connections” has moved. But where has it gone?

The old location

When you click onto “Connections” now you’ll receive the following message:

Management Hub?

Clicking this button takes you to the new Management Hub, the new fourth icon which goes alongside the existing three.

New Manage (Management Hub)

Within this area you now have access to your Linked Services, Integration Runtimes and Triggers. These all have the same options as before, they’ve just moved!

Moved options and New options

You do now have access to more detailed Git configuration options and also the ability to change the Parameterization templates for your ARM templates. This is only accessible when you have a Git repository set up and the Parameter file within it.

You can currently still access Triggers from the main Author window, but I would recommend getting used to finding them in the Management Hub to keep everything together.

Release details of the Management Hub from Microsoft can be found here: https://docs.microsoft.com/en-us/azure/data-factory/author-management-hub

Azure Data Factory (ADF) Parent Activities don’t trigger ON ERROR OUTPUT when Child Activities Error… What happens?

So that title is a big question that a client of ours came across that required some additional investigation… What does happen and how do we get around it?

In the below example we’ll use a simple IF Activity as our Parent Activity. To explain briefly how it functions, you provide it with a query that evaluates to either True of False. This then passes down to a set of Activities If True and a different set of Activities If False. We then have three Outputs, On Success, On Failure and On Completion. This looks like:

(Image 1) Pipeline with If Activity and three outputs

The IF Activity therefore has logically four different things it does given the query criteria:

  1. If the query evaluates correctly, then:
    • If True, Pass to True Activities
    • If False, Pass to False Activities
  2. Error Activity if the query doesn’t evaluate to True or False.

Following that, we have the below options:

  • When 1. If True Activities complete with Success, Trigger On Success Output and On Completion Output
  • When 1. If False Activities complete with Success, Trigger On Success Output and On Completion Output
  • When 2. completes with Error, Trigger On Error Output and On Completion Output

The issue occurs with the below options:
What happens when the If True Activities complete with Error?
What happens when the If False Activities complete with Error?

Logically you would expect that to cause the IF Activity to error as well. Right? WRONG!

Although it shows as if it has an error in the pipeline view:

(Image 2) If Condition Error, without passing down On Success or On Failure?

The details show “Activity failed because an inner activity failed.”:

(Image 3) If Condition Error Message

This means that the IF Activity itself has still started down it’s success pathway and is now just showing the error from further down. This causes the IF Activity to complete with neither Success or Error! (You can see this in Image 2 above). You can also see that the On Completion Output has triggered successfully.

This means that the Error Output of the If Activity can only be used to handle Errors on the Evaluation of the If Query itself. (When it can’t get to true or false, or can’t execute for another reason).

If you want to monitor the steps inside the IF Activity, you’ll need to put those output pathways inside the True and/or False activities themselves and handle this at a deeper level.

I have launched a new twitter account http://www.twitter.com/PurpleFrogReiss so please follow me there for updates on when I post new blog posts!

SSAS Tabular Deployment Wizard fails Newtonsoft.Json error

The Scenario

Deploying an Analysis Services Tabular model to SSAS Azure using the Analysis Services Deployment Wizard. Both Visual Studio 2017 & SQL Server 2017 installed on the client.

Try and click on the ellipses to change the data source connection string or impersonation information results in a Newtonsoft.json error:

“Could not load file or assembly ‘Newtonsoft.Json, Version 6.0.0.0, Culture=neutral, ……”

 

The Solution

If you look at the folder containing Newtonsoft.Json.dll (2C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Extensions\Application” – substitute 140 for your version of SQL Server) and then right click properties on the Newtonsoft.Json.dll file you’ll probably find that it’s version 10.0.3 or later, but the error message shows that the deployment wizard is looking for version 6.0.x. We therefore have to tell the SSAS Deployment Wizard to use a newer version instead.

Use Notepad(++) to open the Microsoft.AnalysisServices.Deployment.exe.config file, which you should find in “C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio” (substitute 140 as above, for your correct version).

Find the section for Newtonsoft.Json

And make a couple of changes (backup the file first!!!)

  • Add in a bindingRedirect line, redirecting 6.0.0.0 to 10.0.0.0
  • Change the codeBase version number to match the actual file version, 10.0.0.0

Save and close the file, relaunch the deployment wizard, and you should find it all works ok.

Frog-Blog Out

 

Lookups and If Conditions in Azure Data Factory v2 (ADFv2)

Azure Data Factory v2 (ADFv2) has some significant improvements over v1, and we now consider ADF as a viable platform for most of our cloud based projects. But things aren’t always as straightforward as they could be. I’m sure this will improve over time, but don’t let that stop you from getting started now.

This post provides a walk through of using the ‘Lookup’ and ‘If Condition’ activities to do some basic conditional logic depending on the results of a database query.

Assumptions: You already have an ADF pipeline created. If you want to hook into SSIS then you’ll also need the SSIS Integration Runtime set up – although this is not relevant just for the if condition.

Scene setting: We want to execute an SSIS package, but only if the package is enabled in a config table, and in a ‘Waiting’ state. We can determine this state by calling a stored proc in a database.

Solution: Use a ‘Lookup’ activity to call the stored proc, with the single row output saved to a Dataset. Then an ‘If Condition’ activity to check the results of an expression, followed by whatever activities you want to call when the expression equates to True or False. the overall pipeline will look something like this.

Now we’ll look at some of the configuration of the activities. Before we create the pipeline we need to add a Dataset (actually a data source), pointing to the Azure SQL DB that holds our stored proc. Click the + to add a Factory Resource, and select Dataset, followed by Azure SQL Database.

In the ‘Connection’ tab, click ‘+ New’ to create a Linked Service, and enter the connection string details to your Azure SQL DB. You don’t need to select a table, as this dataset will just be for calling a stored proc, so leave it unselected. I’ve called my dataset ‘ComponentState’.

Now add a pipeline by clicking the + in the Factory Resources pane but this time selecting ‘Pipeline’.

Firstly add a Lookup activity, and set its Source Dataset to the one you’ve just created, and then select the stored proc from the dropdown list. Or you could write a query here instead. If you’ve selected a Stored Proc, you can click on ‘Import Parameter’ to pre-populate the parameters into the section below.

Note that the ‘First row only’ checkbox is selected by default. This is correct if you only want a single result row. If you want the full resultset then untick this. (Note that the remaining steps here would require different expressions).

Then add the ‘If Condition’ activity, and link the two by dragging the green box from Lookup onto the If Condition activity:


In the Settings tab, we need to specify the condition expression to use. We want to compare the ‘ComponentState’ field of the proc to the string ‘Waiting’. So we use the equals() function, although we could also use ==.

The expression is:

@{equals(activity(‘Lookup1′).output.firstRow.ComponentState,’Waiting’)}

The @{ } denotes an expression that will be evaluated. Anything outside these brackets will not get evaluated, just left as a string. The first parameter to the equals function is “activity(‘Lookup1’).output.firstRow.ComponentState“, or the ComponentState field of the first row of the output of the Lookup1 activity. We then compare this to the hard coded string ‘Waiting’.

This will either return True or False, the outcomes of which we can configure in the ‘Activities’ tab,

In the ‘If true Activities’ section, just add the ADFv2 activities that you want to happen if your expression evaluates to True, and the same for False. Note that when you edit this, you get a complete canvas to add as many activities you want, I’ve just added one here, to call an SSIS package.

Hope that’s been useful.

<Frog-Blog Out>

Power BI Sentinel
The Frog Blog

Team Purple Frog specialise in designing and implementing Microsoft Data Analytics solutions, including Data Warehouses, Cubes, SQL Server, SSIS, ADF, SSAS, Power BI, MDX, DAX, Machine Learning and more.

This is a collection of thoughts, ramblings and ideas that we think would be useful to share.

Authors:

Alex Whittles
(MVP)
Reiss McSporran
Jeet Kainth
Jon Fletcher
Nick Edwards

Data Platform MVP

Power BI Sentinel
Frog Blog Out
twitter
rssicon