Why Power query as a transformation activity in Azure Data factory and SSIS?

This blog post will describe how power Query activity in ADF and SSIS can be useful. As well as, I will share the differences of Power Query activity between SSIS and ADF.

Why Power Query and When to use it?

When data engineer works for transformation pipeline they get different activities like lookup, merge, data conversion etc. in their preferred ETL tool. ETL tools like Azure data factory (ADF) got Dataflow and Databricks to solve complex transformation. In addition, ADF introduced 'Power Query' (previous name data wrangling) as an activity. Please note that, Power query is still in preview for both Azure Data Factory (ADF) and SSIS. 

Fig 1: Power Query in ADF

Despite having many activities in Azure data factory why we need Power Query? Let's share my experience when Power Query have chosen as an activity in the pipeline.  The task was to get data from complex excel files with many calculation and more than 1000 columns which is used by business as an application. Yes! you got it right, it's an excel application, organization still uses excel as an application!!  A few transformed and calculated columns need to go to the modern data warehouse from the excel files. 

In this scenario, thought about what would be the best activity to choose from: DataFlow, Databricks or Power Query? well, I would say all of them may work but Power Query was the best choice.

Let me explain, why? Since the source file is excel and it's got  more than 1000 columns with many calculation inside, It's almost impossible for a Data Engineer to find out how to derive the expected outcome where no mapping or transformation logic is provided. By using Power Query visual transformation, business expert and I were able to work closely and produce the output in a very short period of time. 

Fig 2: Power Query transformation in ADF

 As a Data Engineer, when you work with dataflow or Databricks or any other transformation activity in ETL tool, you follow the documented mapping logic and build the pipeline. It means transformation rules and mappings are predefined. However, when transformation rules are yet to discover then best to start with Power Query. You can simply start with Power BI desktop to work together with business to produce the expected outcome. And when output is verified and accepted then then copy the M Query to ADF Power Query activity or SSIS Power Query source. In fact, now you have the transformation rules in the M Query so if you like to use other transformation activity like dataflow or Databricks you can use that too.

What works in SSIS but not in ADF?

Power Query activity is in Preview for both SSIS and ADF, however; if you choose ADF then you need to convert the source file from .excel to .csv since Power Query for ADF doesn't support .excel as source dataset.

Fig 3: Source dataset for Power Query

However, if you work with Power Query in SSIS then it support excel as source. On Contrary, in SSIS; when you are working with Power Query Source, it doesn't have user interface to make the transformation like ADF. The obvious reason is, you can use Power BI desktop to do the transformation and then copy the M query (Power Query generate M syntax which called M Query) from Power BI and paste it to Power Query Source in SSIS.

Fig 4: Power Query in SSIS

In summary, Power Query in both SSIS and ADF is useful Activity and new feature which still in preview, hence there might be many different scenarios where you want to use Power Query activity, however; this article is based on my experiences with Power Query activity in ADF and SSIS. It's also interesting to know that, The user interface you get under ADF Power Query is identical to Power BI, however, not all M query is supported by ADF Power Query yet.

How to deal with NULL in ADF dataflow compared with SSIS?

When you are working with ETL/ELT, sometimes you may need to transform NULL into something meaningful value. If you worked with SSIS, you know how you handle that. This blog post will describe how do we do in SSIS and how the very same task can be done in ADF Dataflow.

 Consider, we have a .csv file where Name columns have NULL value for 2nd record (figure: 1.0)

Fig 1.0: Sample .csv file with NULL record

After connecting the .csv file through flat file source in SSIS data flow, we can debug and view the record through data viewer which will look like below figure 1.1

Fig 1.1: Result in SSIS data flow - data viewer

If you would like to replace the NULL value with meaning value, in that case you need to use derive column activity and use expression.

SSIS data flow expression got REPLACENULL function, which will replace NULL to the expected value that you want.

The expression: REPLACENULL(Name,"Unknown")

The above expression will return 'Unknown' when Name is NULL otherwise it will return the original value.

Fig 1.2: Expression in SSIS Data flow to replace NULL with 'Unknown'

When it comes to ADF data flow regular expression similar like SSIS expression; isNull only give you true or false. And isNull function take only one argument, e.g. below fig 2.1 took the argument Name and return True (✓) if the value is NULL.

Fig 2.0: ADF dataflow isNull function

Now, let's find out how to transform NULL value into something meaningful in ADF data flow. ADF doesn’t have the same function REPLACENULL which used in SSIS, rather there are two ways you can replace the NULL values in ADF dataflow.

Approach 1: Combination of iif and isNULL function

Expression: iif(isNull(Name), 'Unknown', Name)

The function iif will check the condition isNull(Name), if Name have Null value it will return 'Unknown' otherwise original value will be returned.

Fig 2.1:  using iif and isNull in ADF dataflow

Approach 2: By using iifNull function

The smartest solution is to use iifNull which will return exactly the same result we found via approach 1.

expression: iifNull(Name, 'Unknown') will return 'Unknown' if Name have NULL values otherwise it will return original value.

Fig 2.2:iifNULL function to replace NULL value

In summary, expression is similar to replace NULL values for both SSIS and ADF data flow, however, the function you need to use is different for two different tools.

Environment variable setup in SSIS Catalogue

If your SSIS solution is built with project deployment mode(not package deployment mode) and would like introduce environment variable then this post is for you.

Let's start with a few steps before going into environment variable. In general, after building  SSIS solution, you get .ispac file which you take from DEV to UAT/TEST/PREPROD/PROD environment. And then by following the wizard you deploy the .ispac file under SSIS catalogue.  To know step by step process to deploy SSIS package to catalogue please follow : Deploying SSIS package to Catalogue

After deployment , you will find the packages under SSIS catalogue, as like below figure 01:

Fig 01: SSIS Catalogue

At next step you will create  job/Jobs  to run the packages. Now say, if you don't create any environment variable then the job will like below:

Fig 2: Job Properties without Environment variable

Will this job execute without environment variable? Surely, it does. But the connection string still retain development server information. However, the solution is already moved to UAT or Test Environment, so you need to change the connection string manually.   If  Environment variable have been defined then it can be assigned to the job so the same job (process) can run for different environment (UAT/Pre-PROD/PROD) by pointing the right environment.

Job with environment variable look like below figure 3:

Fig 3: Job with Environment variable

At this setup, you can choose from different environments before running the job. And each environment can hold different source and target database as well as source and destination file location. Now let's see how to create the environment  variable.


Step 1: Create environment variable
Under SSISDB, you will find Environment folder as like below picture, right mouse click and follow the step.

Fig 4: Create environment variable

Step 2: Property set up for environment variable

At this step, you will define connection strings, source/destination file path etc.

Fig 5: setup properties for environment

Step 3: Connect environment variable from project, Under SSISDB catalog choose the project and then right mouse click to find 'Configure'.

Fig 6: Configure environment variable

And then add reference to the project, References->Add and then environment can be chosen.

Fig 7: adding environment variable reference to the project

Step 4: From step 1 to 3, we worked with SSISDB, Now let's look at the job, SQL Server Agent->Jobs, where will have option to choose from environment

Fig 8: Job Properties

And from the properties, click on Steps and then you will find variable to choose from.

In this example, only UAT environment have been created but as per your need Pre-PROD, PROD can be created and source and target database can be assigned accordingly. And those environment can be assigned to job.

SSIS: Execute SQL Task Limiation

I would like to share an experience that found recently. In general, when you work with SSIS and using SQL Execute task, you tend to make query by using  SQL Server Management Studio (SSMS) and when query works fine then copy the code into the Execute SQL Task. However,  when I copied the code and pasted to Execute SQL task and run the package it failed at Execute SQL task, then figure it out only part of the SQL code is pasted.

Fig 1: SQL Execute Task

The SQL code I copied was around 1600 lines, however when pasted it's only took 891 lines of code to Execute SQL Task and rest have been discarded. It means Execute SQL task got limitation, out of curiosity took the pasted part of the code from Execute SQL task then count it and found it can hold up to 30,987 characters with space. (see below fig 2)

Fig 1: SQL Execution task limiation

How do you solve it?
Well, It's easy, you wrap the SQL code either by using view or store procedure and then use that in your Execute SQL task.

Table variable using in SSIS packages

When you need it and how to use it?

As you may know table variable is similar to temp table in Microsoft SQL, however; table variable generally uses fewer resources. I am going to explain when table variable could be useful at the time of SSIS package development.

Scenario 1: When you need to store data like array and need to use it later in your SSIS package

For example, below scenario I only had to insert/update data where the periods exist in the source data and then making next operation depend on the value exist or not.

DECLARE @periodACD TABLE (periodACD char(2))

INSERT INTO @periodACD

SELECT Distinct [PERIOD]

FROM [ETL].[SourceTable];


SELECT CASE WHEN periodACD='4' THEN 'Insert or update for April'  END /*You can use any transformation or calculation at THEN clause.*/
FROM @periodACD WHERE periodACD='4'


Scenario 2: Keep insert or updated row count for logging purposes while you use SQL Merge

For example, you are using merge to update the dimension and want to catch the number of rows you insert, you can use table variable to store the data for merge.

DECLARE @RowCount TABLE (InsertedID INT) --Declare Table variable

MERGE  [Dim].[Table] AS Target
 USING    [Staging].[Data] as Source
    ON Target.SourceID = Source.SourceID --BusinessKey
WHEN MATCHED

AND (ISNULL(Source.SourceName,'')<>ISNULL(target.[BrandDesc],'')
OR ISNULL(Source.SourceDate,0)<>ISNULL(Target.SourceDate,0)
        OR ISNULL(Source.SourceBit,'')<>ISNULL(Target.SourceBit,'')
   
THEN
   UPDATE SET Target.SourceName = Source.SourceName,
  Target.SourceDate = Source.SourceDate,
  Target.SourceBit = Source.SourceBit

OUTPUT inserted.SourceID 'InsertedID' INTO @RowCount; --output IDs for rowcount

SELECT COUNT(*)
FROM @RowCount

New to Git and Github? How Azure DevOps connect with Git? Must know.

Those who have been using TFS for a long and now you know 'Git' is a 'thing' that you can't avoid.  When you start with Git, please don’t expect it will be exact same like TFS. When I have started with TFS 10 years back, it was super easy to start with, however for Git, start with a mindset that, command line is required.

As you know Git, other name come along which is 'GitHub' and then confusion grows. 
Many people think Git and GitHub are the same, But it’s not. Git is the version-Control software and GitHub is your storage with sharing capability. If you don’t want to share your code with anyone but still having versions maintenance you don’t need GitHub (which is unlikely though). Moreover, alternative to GitHub you can use Azure DevOps.

Those of you used to work with TFS, maybe already got to know about Azure Devops which give you option to either use Git or TFS as your version-control tool. It means in this scenario, you don’t need GitHub. Below picture depicts Git relate with Azure DevOps and GitHub.

Fig 1: Git, GitHub and Azure Devops

Let's start with beginner steps to work with Git.

Step 1: Install Git at your PC.
Follow the link and choose your right platform to download the file and then install it.
https://git-scm.com/downloads

Step 2: Open the Git command window, which look like below

Fig 2: Installed Git software

Now, you would like to create a solution or file which you would like to add to the Git and would like to work with other colleagues.

Adding file to Git means, the folder where you keep the files/code should be recognized by Git. To achieve that, you need to do following:

For example, your code is located under C\Code and you want to make this as Git repository.

After opening the Git Bash, write below command:

1 cd C:\                                                                                                            

2 cd Code

3 git init

Now the folder is ready to use as Git repository

'Initialized empty local Git repository in C:/Code/.git/'

And you will a find a git folder created in the folder C:/Code/

Now you can copy all your files under the folder C:/Code and let's add a file to the local Git repository.

4. git add Testcode.txt

and Commit the code in local repository

5.  git commit -m 'added test file'

Now your solution or all code files is under git repository, If you work on your own and wanted to have version control so you can always go back to your earlier code, which is ready at this stage.

Step 3:
Let's get to the next step where you would like to push this code to either Github or Azure Devops. In my case I have uploaded the code to Azure DevOps.

How do you do this?

Login to the Azure devops : https://dev.azure.com/

Create repository , choose Type as 'Git'

Fig 3: Create repo under Azure Devops

After finishing the repository creation, you will find like below window, where you need to copy the command line code.

Now go back to your  'Git Bash' command window, and write two lines of code:

6. git remote add origin https://d*****@dev.azure.com/dpaul0628/DpaulGithub/_git/LoveGit

7. git push -u origin –all

Now if you login to the Azure DevOps, you will see the local file you have committed earlier is uploaded to the Azure DevOps.

Misleading popup message in SSIS solution can give you trouble!!

SQL Server Data Tools (2017) is smart and trying to help you many ways. But sometime performance improvement popup message can lead you to some other issue.

I would like to share my experience recently, I was working with SSIS packages and at the end of the day commited and pushed the code to github successfully. But very next day when I opened the solution, surprisingly got incompatibility error (Fig 1), I was not able to open the project.

Fig 1: Solution incompatibility error.

I was really surprised, and trying to remember what I did wrong which may causes the issue. The project incompatible (Fig 1) generally happens when Visual studio version miss match. I did not do any update !! My mind was stuck for a while then recall that I clicked a pop up message in SSDT last day even can't remember what I responded. 

The pop up message was the below one (Fig 2). And I surely clicked 'Disable this extension'.

Fig 2: Misleading pop up

Pop up message in SSDT also says 'Disabling it may improve your experience' (Fig 2). I did not pay close attention and trusted the part where saying it may improve the performance which is misleading. And this causes the solution turned into incompatible.

How to solve this issue?

There are two ways you can solve:

1) Repair the software or 2) Enable the extension

1) Repair the software

You can execute the SQL server data tools (SSDT-Setup-ENU.exe) and click repair as like below figure 3, complete a few steps in wizard. When repair is done then open the solution, now compatibility error will go away.

Fig 3: Repair SSDT

2) Enable the extension

Please open SQL Server Data Tool and go to Tools->Extension and updates. You will find 'Micoroft Integration Services Project' extension is Disabled (Fig 4), so you need to enable the extension,

Fig 4: Extension and update

Unions in OLEDB Source can kill performance in SSIS, How to handle this?

If you write SQL with many UNION in OLEDB source transformation then you will find the SSIS package is running slow.

For example, you may require many SQL unions to populate the data set as like below:
SELECT x,y,z,z1,z2
FROM tblA

UNION
SELECT x,y,z,z1,z2
FROM tblB

UNION
SELECT x,y,z,z1,z2
FROM tblC

UNION
SELECT x,y,z,z1,z2
FROM tblD

UNION
SELECT x,y,z,z1,z2
FROM tblN

Fig 1: OLEDB source 

Instead of using the above SQL in OLEDB source, you can use Union All transformation which is much faster in compare with using the SQL code in OLEDB source.

Fig 2: Union All Transformation

How to deploy SSIS package to Sql Server Integration Services Catalogs (SSISDB)?

Though there are different methods to deploy SSIS package, however; this blog post will cover how to deploy SSIS package to SSISDB. So far found, deploying to SSISDB is considered as best practice. SSISDB is introduced with SQL server 2012. The advantages you can have by using SSISDB as follows:
1) Easy to deploy from one environment to other
2) Report facilities in the SSISDB, so you can see execution time, error or whatever happend to your packages.
3) SSISDB is protected so your connection string and sensitive data is safe.

As per MSDN, "The SSISDB catalog is the central point for working with Integration Services (SSIS) projects that you’ve deployed to the Integration Services server. For example, you set project and package parameters, configure environments to specify runtime values for packages, execute and troubleshoot packages, and manage Integration Services server operations.The objects that are stored in the SSISDB catalog include projects, packages, parameters, environments, and operational history."

Let's divide the topic into two section 1) Creating Catalog 2) Deploy package to the catalog

Section 1: Creating catalog

step 1: Log into you Sql Server management studio and you will find the folder "Integration Services Catalogs", please create catalog as like below  fig 1:

fig 1: Create Catalog

Step 2: Create folder under Catalog:
Right click the newly created catalog 'SSISDB' and then create new folder with the different layers (e.g. staging, edw layer, data mart layer etc.)

fig 2: Folder under catalogue

After you create folder you will find  two new folders automatically created under the folder. It means if you create a folder called 'DSA' then you will find two folders  'Projects' and 'Environments' as like below Fig 2A

Fig 2A: folder under SSISDB

Section 2: Deploy package to the catalog

The below steps will assist you to deploy the package to the catalog.

Step 1: Build the solution and I am sure you all know how to build solution ;)

fig 3: build the solution

Step 2: Locate the .ispac file

fig 4: .ispac file

Step 3: Follow the wizard to deploy

Double click the .ispac file and then you will find below wizard:

fig 5: 1st page of the wizard

Choose the option project deployment and browse the .ispac file that you want to deploy.

fig 6: project deployment

Now you reach at the destination wizard where please  provide the destination server name (SQL server name where you will deploy the package) and then browse the path. When you click the browse for path you will find folder list from SSSIDB catalog (the folder you created at the section 1, step 2 of this post)

fig 7: choose destination path

So your package deployment is done to the SSISDB catalog.

SSISDB catalog has the opportunity to monitor the packages, you can create report from it. please find the below figure to generate the report:

fig 8: SSISDB catalog report

Step by step guideline for installing Visual studio 2017 for BI Developer

It is easy to install Visual studio in general, you make a few clicks, follow the wizard and then you are done. However, when it comes to Microsoft BI tool installation then last couple of years it changes a few times. For example earlier (before VS 2010), the BI tool called  Business Intelligence Development Studio (BIDS) and it was part of SQL Server installation.

But when Visual Studio 2010 release, you got new name called 'SQL server data tools (SSDT)' and which you had to install top of Visual studio..

 Fig 1: SQL Server Data Tools menu

When you open SQL Server Data Tools (SSDT) you find the template like below to develop BI solutions.

Fig 2: Templates under SSDT

However, Visual Studio 2017 release you will not have fully functional SQL Server Data Tools as separate instance. The template for making BI solution (SSIS,SSAS, SSRS) will be integrated inside Visual Studio 2017. Being said so, the release version 15.3.0 preview also have possibility to install as separate instance. However, stand alone instance breaks every time if you try to connect with TFS.

Enough talking. let's start with the Visual Studio 2017 installation steps for BI Developer:

Step 1: First install the Visual Studio 2017

Run the VS 2017 setup package and you will find screen like below to choose for BI development:

Fig 3: choose what to pick

Below image will give you closer look of what have been chosen for BI development.

Fig 4: Closer look -what have been chosen for BI

After you select the boxes you follow the wizard and will be completed the VS 2017 installation.

Step 2: After completing the VS 2017 installation take the SSDT file downloaded from (https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt). If you already have the SSDT exc file then please avoid dowloading the file.

When you run the SSDT-Setup-ENU.exe file , you will find below window to choose from :

Fig 5: Option to choose for installing separate instance for BI

If you want templates for BI development should be place inside Visual studio 2017 then use the first option; but if you want separate instance then choose the second option from drop down list which is: "Install new SQL Server Data Tools for Visual Studio 2017 instance"

I have installed separate instance at first go and installation went fine, under your desktop menu you will find Visual Studio 2017 (2) as like below:

Fig 6: Separate instance only with BI Templates

Though separate instance installation went fine, however; the instance was crashing every time I have tried to connect TFS. Then I uninstalled it and installed with the option selected " Visual Studio Community 2017" (Look at the figure 5). And I get BI templates inside the Visual Studio 2017 which look like below:

Fig 7: BI templates inside VS 2017

Now you are ready to make your BI solutions by using brand new Visual studio 2017 and it also works integrating with TFS.

SSIS Error: Cannot convert unicode and non-unicode string data types

At the time of data migration or at any situation you may need to add new column and assign fix value to the column. After you have added the column and put the static/fixed value, then you may receiving the error: "Cannot convert unicode and non-unicode string data types"

 Let's make an example; business delivered excel file with all data from legacy system which is already closed; and  you need to add the old data to your data warehouse. Loading data from excel file to a staging table;you built the package as like below figure 1, where you have added data flow component 'Excel file source' and you have 'derived column' to add new column and assign a hard coded value.

After you design it found the red error like below that you may hate.

fig 1: SSIS package migrated data

Well, let's look it closer and you can see the error saying  as like below fig 2: "Cannot convert unicode and non-unicode string data types". But why we see this error?

Fig 2: Unicode and non Unicode miss matched

We made a new column by using derived column component called 'Status'  and we set 'Completed' for the column Status which is extracting from the excel since all extracted data are old and transactions are completed so we set status as completed. However, the static string 'Completed' set as data type Unicode String [DT_WSTR] in SSIS which has issue with the target table column 'Status' data type. The  staging table has staus column as varchar which cause the issue here.

Fig 3: Data Type issue

If you look at the above figure 3, where string "Completed" is set under the Expression so DataType has been picked as [DT_WSTR] which is Nvarchar means Unicode. However, the staging table we have created in the database, the particular column is defined as Varchar which is DT_STR for SSIS means non Unicode. So it's clearly difference between [DT_WSTR] and [DT_STR] in SSIS.

How to solve it?

To solve the miss match of Unicode and Non Unicode issue, we need to convert unicode data type to Non Unicode. So here goes the conversion expression :(DT_STR,17,1252) "Completed". "Completed" string is considered as Non Unicode/Varchar data type. 

Fig 4: Solve issue with data conversion

If we explain the expression (from Fig 4),  where (DT_STR,17,1252) -> (DataType, length of the data, Code page), Code page 1252 means it's windows Latin 1 (ANSI).

After you add the above expression the error will disappear and you can run the package to load the data.