SQL Knowledge Bank - Azure

Introduction Pipeline is ready and we already publish it, now we need to trigger our pipeline in a schedule interval. For SSIS Developer, we have SQL Server Agent job to schedule and run the SSIS Project or Package.   Scheduling Jobs in SQL agent have some limitation. We have to logically check the SSIS Package execution is completed or not before proceeding to next run of same or dependent project. It also has some flexibility by using Steps. Suppose we need to run a SQL Script on Step-1, then SSIS package on Step-2. We are trying to say that dependent activity can run step by step. In Azure ADF triggering, we have some options that we not find in SQL Agent Job, so both have some advantage and Limitation also. In this blog post we are going to discuss ADF Add Trigger options. Hope it will be interesting.   Where we get the Add Trigger     When we open the Pipeline we can get the Add Trigger button beside the Debug button. After clicking this butt...

Introduction lot of my friend from different group is asking to provide a blog post related to incremental load. If you search Google related to this tropics, you can find bundle of blog post. All are the same using water mark technique. But in my view point it is not a practical approach for incremental load. When we talk about incremental load, we assume that we have billions of records in source which is ever changing and we need to move on destination table. Here in this demonstration we used ADF control flow but it can be done with any ETL or ELT tools like SSIS. One last thing that I would like to mention if we try to load huge records from source to designation, it definitely gives us performance problem or stuck in between. So, we have to load the data in a smaller chunk or batch. We have to keep in track that data that we already loaded in the destination is going to updated in source or not. If it is going too updated, we have to update the destination accordingly. Yo...

Introduction SQL WHERE clause we often use LIKE operator with wild card character (% or _). But in ADF we can’t use it directly.  We can use heard coded LIKE and wildcard character in Control flow Lookup Activity, but we need to automate the process. Hope it will be interesting.   Case Study We have a Flat file named DeveloperList.txt and a AZURE SQL Table object named [dbo].[WorkCommesta]     Now we need to search each [ DevName ] from DeveloperList.txt to our [dbo].[WorkComments].[comments] table using wild card and LIKE operator. It just likes SQL command:   Select * From Workcommensts WHERE comments LIKE '%Joydeep%'   Stored Procedure     ADF Control Flow     Outer Lookup Activity     ForEach Activity     Inner Lookup Activity (Under ForEach)     Set Variable Activity (Under ForEach)   Stored Procedure Activity (Under Fo...

Introduction In this article we are going to discuss about Activity Dependencies at ADF Control flow. As a SSIS developer we all have Experience about Precedence Constrains and how useful it is when building SSIS control flow. In ADF we have the similar concept with some limitation. Depends on this, we have to design our control flow in ADF. Hope it will be interesting.   Precedence Constraints in SSIS Before moving further, we have to review our SSIS Precedence Constraints.   Here we have: 1.       Constraint – Success, Failure, Completion 2.       Expression 3.       Logical AND / OR   It is really flexible.   Activity Dependency in ADF     In ADF Activity Dependencies provides only Logical AND not any Logical OR. It has Add Activity on: 1.       Success 2.       Failure 3.    ...

Introduction SSIS developer, we all know the importance of Lookup Transform . Here in ADF, we have two types of Lookup. One is for control flow and other is for data flow. The behavior of both the lookup is not same. Control flow lookup provide only record sets but in Data flow we are using it as for looking up some records from other data sets. In ADF data flow Lookup feature is more than the SSIS Lookup Transform. So, in this article we are thinking to demonstrate all the functionality of ADF Data Flow lookup with a simple example.   Hope it will be interesting.   Case scenario We have a Flat file which contains Employee Code, Salary and Bonus Information. There are multiple Flat files in our Azure BLOB Container. Now we have a JSON file which contains information about Employee Code, Employee Name and Employee Email. Now we have to use the Lookup Transform to find the Employee Name and Employee Email from our JSON file.   About Data Flow Lookup Be...

Introduction In this blog post, we are just going to Join and Join but in logical manners to understand ADF Data Flow. Here in ADF Data flow we are using Flatten, Select and Join Transform. Hope it will be interesting.   Case scenario We have some Flat file which contains Employee Code, Salary and Bonus Information. There are multiple Flat files in our Azure BLOB Container. Now we have a JSON file which contains information about Employee Code, Employee Name and Employee Email. We also have table object named Employee State. Which contain Employee Code and State name.     Now we have to join all using ADF data flow. First we used Get Meta Data activity to collect all file information. By using ForEach we are moving one by one file. Within the for Each we have Mapping Data Flow activity which actually call our data low. Rest are done by Data Flow.   ADF Solution   Control Flow Structure     Data Flows Structure  ...