0845 643 64 63

Monthly Archives: February 2022

Execute SQL Task in ADF

If you’re new to ADF or Synapse pipelines and looking for a way to execute SQL commands in pipelines, unfortunately there is no equivalent to the Execute SQL Task in SSIS, but I have found some alternatives which work well for me.

For something as simple as a SELECT statement where you want to return results to be used later, you can use a Lookup activity:

You can then reference the output of this activity in other activities using dynamic content such as @activity(‘LookUp’).output.firstRow.DateKey.

Although you can also use the Lookup activity for DML commands, it is not designed for this and so I’ve found another way to do this. First, create the following stored procedure in the database:

This stored procedure will execute the SQL command passed in via the @sql parameter. Now you can use the Stored procedure activity to point this newly created stored procedure and pass in a SQL command as shown here:

This activity will now execute the SQL command stated in the parameter value. Using this approach helps keep things consistent across your pipelines for SQL commands as they all use the Stored procedure activity, and the only difference is the SQL command used in the parameters section.

You can also add dynamic content to dynamically build up the SQL command that is passed into the stored procedure, this can allow you to use the same configuration for multiple Stored procedure activities but with different outcomes. For example if you had pipeline variables, the above TRUNCATE TABLE command could be replaced with @concat(‘TRUNCATE TABLE ‘,variables(‘SchemaName’),’.’,variables(‘TableName’)) and this would truncate a different table per pipeline based on the variable values.

Note: there is a limit on the number of characters the parameter value can take, so if you have a large SQL command it is best to create a stored procedure specifically for that command and to reference that stored procedure in the activity.

Pretty simple in the end, I hope this helps!

Creating a quadratic solver in Python

In this blog post, I will be showing you how to create a python function that will solve any quadratic equation.

Firstly, what is a quadratic equation?

A quadratic is an algebraic equation that can be arranged as ax2 + bx + c = 0 where a, b, c are known and a ≠ 0. If a = 0 then the equation becomes linear as there isn’t a x2 term.

Secondly, how to solve this equation? There are different ways to solves quadratic equations including:

  • By inspection
  • Completing the square
  • Quadratic formula

We will be using the quadratic formula which is:

Where a, b & c are the known coefficients when the equation is arranged into ax2 + bx + c = 0

We use the Quadratic formula because it can be used in any situation and there is no nuance to it like the other methods.

The first part of the code is to define a function that will accept 3 parameters: a, b & c

To use the quadratic formula, we will use the square root function from the imported math module.

Before plugging the parameters into the formula, we can work out how many real roots we will get by using the discriminant. The discriminant is b2 – 4ac.

If b2 – 4ac > 0 then there are 2 real roots.

If b2 – 4ac = 0 then there is 1 real root.

If b2 – 4ac < 0 then there are no real roots. (There are complex roots using imaginary numbers, but we won’t go into that here, maybe in a part 2)

Let’s add calculating the discriminant and an IF statement depending on the result to the function.

Next, lets plug a, b & c into the quadratic formula.

When the discriminant = 0 we only need to find the value of root1 as it’s equal to root2. Knowing the discriminant = 0, we could simplify the equation to

In the code I have chosen to leave the full formula in.

That’s our code complete, finally let’s test the function with the 3 different discriminant scenarios.

The function works as intended, the code is attached as a txt file.

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)
Jeet Kainth
Jon Fletcher
Nick Edwards
Joe Billingham
Lewis Prince
Reiss McSporran
Microsoft Gold Partner

Data Platform MVP

Power BI Sentinel
Frog Blog Out
twitter
rssicon