Introduction: In order to move your Pipeline from one environment to other you require your Pipeline to be saved on Github. This Blog will guide you through how to connect your Azure Data Factory Pipeline with your Github account. Step 1: Create a new Azure Data Factory and Tick the Enable GIT checkbox.     Step 2: Create a new Repository in your Github Account.     Step 3: Copy the URL from of the newly created repository from the address bar.     Step 4: Enter the GIT URL, the repository name which you recently created, branch name (usually master) and your root folder (usually same as your repository name) and click on Create.     Step 5: Now you must authorize your GIT Account on ADF. You will be promoted to Login with your Github Account. Do the necessary and click on Authorize Azure Data Factory.     Step 6: Now you must select the Working Branch. We are using the default master branch. Click Save and then you can proceed to create your Pipeline.     Conclusion: Each Time you publish the changes in ADF they are simultaneously stored in the GIT repository’s branch.

In order to copy data from Dynamics AX to On-Premises SQL Server, we need certain prerequisites – Login into https://portal.azure.com/#home Create a Data Factory and Click on Author and Monitor. Go to Author and create the following- Connections (Linked Services) Datasets Pipelines CONNECTIONS         SOURCE Click on New Linked service and create a source connection. Give it a name, Description (optional) and select AutoResolveIntegrationRuntime. We select AutoResolveIntegrationRuntime as our source is cloud-based (Dynamics AX), If it is on-premises, then only we need to install and select Self-hosted Integration runtime. URL: OData Endpoint AAD resource: Same as URL without /data Service Principal ID:  Application Id from App Registrations. Service Principal Key: Value from Certificates and Secrets in App registrations.        SINK In our case, Sink is SQL Server so we enter the SSMS credentials to connect to our on-premises SQL Server. Install the Integration runtime to provide and establish connection of on-premise SQL Server with the cloud. Test the connection. INTERGRATION RUNTIME We can choose between Express-setup and Manual Setup to install the integration runtime. Then go to Dynamics AX, and grant this service principal proper permission to access your Dynamics AX. In order to grant permission, go to Dynamics AX -> System Administration and add your Client ID there. DATASETS Create Dataset for Source and Sink. For the source dataset, select the source connection from the drop down for Linked Service field. In the path field, Select the desired entity. For the sink dataset, In the table field, you can either select a specific table to copy your data or check the auto-create option to make a new table in the sink. Check the Edit option in the Table field and give the desired name of the table to auto create it.  

Introduction We are performing Integration of Accounts from CRM to SQL using ADF Copy activity pipeline. We want to upsert the accounts instead of inserting duplicate records again. Step 1: Auto create the Table named “accounts” in SQL Server during the first Integration run by selecting the Auto create table option.   Step 2: Create a custom data type named “AccountType” using following query.   CREATE TYPE AccountType AS TABLE( accountid uniqueidentifier, transactioncurrencyid uniqueidentifier, address1_city nvarchar(MAX), createdon datetime2(7), accountnumber nvarchar(MAX), name nvarchar(MAX), address1_country nvarchar(MAX), address1_composite nvarchar(MAX), telephone1 nvarchar(MAX), emailaddress1 nvarchar(MAX), websiteurl nvarchar(MAX), primarycontactid uniqueidentifier ) Step 3: Create a Stored Procedure named “spUpsertAccounts”.   CREATE PROCEDURE spUpsertAccounts @account AccountType READONLY AS BEGIN   MERGE dbo.accounts AS target_sqldb USING @account AS source_tblstg ON (target_sqldb.accountid = source_tblstg.accountid) WHEN MATCHED THEN UPDATE SET accountid = source_tblstg.accountid, transactioncurrencyid = source_tblstg.transactioncurrencyid, address1_city = source_tblstg.address1_city, createdon = source_tblstg.createdon, accountnumber = source_tblstg.accountnumber, name = source_tblstg.name, address1_country = source_tblstg.address1_country, address1_composite = source_tblstg.address1_composite, telephone1 = source_tblstg.telephone1, emailaddress1 = source_tblstg.emailaddress1, websiteurl = source_tblstg.websiteurl, primarycontactid = source_tblstg.primarycontactid     WHEN NOT MATCHED THEN INSERT ( accountid, transactioncurrencyid, address1_city, createdon, accountnumber, name, address1_country, address1_composite, telephone1, emailaddress1, websiteurl, primarycontactid ) VALUES ( source_tblstg.accountid, source_tblstg.transactioncurrencyid, source_tblstg.address1_city, source_tblstg.createdon, source_tblstg.accountnumber, source_tblstg.name, source_tblstg.address1_country, source_tblstg.address1_composite, source_tblstg.telephone1, source_tblstg.emailaddress1, source_tblstg.websiteurl, source_tblstg.primarycontactid ); END Step 4: Enter the Stored Procedure Name, Table Type and Table type parameter as shown in the image below:     Step 5: Publish all the changes and debug your Pipeline.  You can Verify the results in SQL Server “accounts” table.  

REST API is an Application Program Interface that uses HTTP request to GET, PUT, POST and DELETE data, it’s an architecture style approach to communicate with third party application. In order to integrate our module with third party applications or in order to pull data from third party application to our database for analysis purpose the REST APIs are useful. For the analysis purpose we can consume REST API with SSIS using third party connector, but the problem with this approach is that, it will be required On Premise server for package deployment and job schedule. The alternate approach is to use serverless CRON expression. Since, mid 2018 the serverless development methodology is ditching the traditional development. In this blog we are going to see how to consume REST API with Serverless CRON expression. Here, we are using Zoho People API for integrating HR modules data to Azure DB. With Zoho People API, you can extract employee’s data and form data in XML or JSON format to develop new applications or integrate with your existing business applications. Zoho People API is independent of programming languages which helps you to develop applications in any programming languages (reference). Implementation Authentication token generation In order to access Zoho People API, Zoho People authentication token is required. The token can be generated using Browser mode and API mode. For the API mode each request has Username or Email and Password needs to include in the POST body Another approach is to register your app with Zoho by going to zoho.com/developerconsole and Add Client ID, Once added it can be used to generate new access_token. We can check the response by use using POST request, Open any REST Client and send request by using any REST API method, here we are send request to get leave data and we are using VS Code REST Client. If the request is valid then the we can get response in following format containing the data requested. 3. As we are getting proper response, the next step is to create CRON expression (Serverless approach) to pull the data from REST API, here we are going to use App services on Azure to create Timer Trigger Function App. The CRON expression is a time-based job schedular. which has six field to define time precision in following format. {second} {minute} {hour} {day} {month} {day-of-week} Each field can have one of the following types of values: Type Example When triggered A specific value “0 5 * * * *” at hh:05:00 where hh is every hour (once an hour) All values (*) “0 * 5 * * *” at 5:mm:00 every day, where mm is every minute of the hour (60 times a day) A range (- operator) “5-7 * * * * *” at hh:mm:05, hh:mm:06, and hh:mm:07 where hh:mm is every minute of every hour (3 times a minute) A set of values (, operator) “5,8,10 * * * * *” at hh:mm:05, hh:mm:08, and hh:mm:10 where hh:mm is every minute of every hour (3 times a minute) An interval value (/ operator) “0 */5 * * * *” at hh:05:00, hh:10:00, hh:15:00, and so on through hh:55:00 where hh is every hour (12 times an hour) 4. The next step is to create function app, for development purpose we are going to use Visual Studio Community 2019/2017. In Visual Studio create a project by selecting File > New > Project Select Visual C# (here we are going to use C# for development you can choose Php, Python or F#) any of your choice. Under the Visual C# select the Azure Functions and click on Next The next step is select a new Azure Function Application, Select the Timer trigger function choose your Azure Function version (v1, v2 or v3). Select Authorization level as Anonymous as we don’t want to include API Key for the function and kept the other settings as it is. After clicking on Create button, it will create the Azure Function App Solution for us, the directory structure is as follow. The next step is to publish the function on Azure, it will deploy your function on IIS and Azure. That we will see in next part of this blog.    

In this blog we’ll see how t publish the and deploy the Azure function on App service. With Continuing with the previous part of this blog Right Click on the solution and click on Publish, it will ask to select the proper subscription, resource group. Select the option relevant to you. Following are the Publishing option available for us. Publish on Azure App Service Azure Virtual Machines File system Local webserver (IIS) Note:- Make sure you have sign in with proper credential and cloud explorer is connected. Once we publish the function, we can check Azure function by going to the https://portal.azure.com/ then search for the Function App. We can see our function app in read only mode, since we have deployed it from visual studio environment. The next step is to create the development logic for the data pull from the Zoho People. Make sure to add all reference libraries required for the development, we can manage the NuGet package manager by going Right clicking on the solution and select manage NuGet package. Make sure to add all required reference to your script file. using System; using Microsoft.Azure.WebJobs; using Microsoft.Extensions.Logging; using Newtonsoft.Json; using System.Data.SqlClient; using System.Net.Http; using System.Net.Http.Headers; using System.Collections.Generic; using System.Linq; As our destination is Azure DB so, we need to create the environment variable to store ADO.NET connection string in settings.json file or by clicking on Azure App Service Settings Then click on Add Setting and then add the setting name like in following diagram we are creating setting for the Azure DB connection. Click on ok and provide the value for the setting. We can get azure connection string by going to the Azure Portal search the DB name and then click on the connection string and copy the ADO.NET connection string, replace the {your_password} with your server password. After adding the environment variable, we can check this in settings.json file The Next step is to implement the code logic and debug the code on local. That we’ll see in final part of this blog.

In this part we’ll see how to Debug the code on local. Once we done with the configuration settings the next step is to call the REST API in our code, here we are using C# for the development. Once we done with the code logic, we can run the code on local by adding the breakpoint The Azure Function app will launch in a local Functions host, and your trigger will be available locally on http://localhost:7071/api/Function1 default listening port is 7071, we can also change the port number by going to the project setting and setting Application arguments to the project settings. host start –pause-on-error –port 7079 We can also launch multiple apps at the same time, we can do this by adding multiple breakpoint at the same time and then start the function without debugging and start another function through debugger and attached the debugger to function app by, Select Debug > Attach to process. Search for fun The greyed-out function is in running state and other is running through debugger. Select the other and click Attach and then we can open the browser and run the both function app different port gets assigned to each function. Publish the App on the Azure and make sure to add the all the parameters on the configuration by going to the Configuration on the Function App.   Note:- For every changes on app we need to build and publish the app.

In this blog I am going to explain you how to store error logs using Stored procedure activity when Azure Data Factory Pipeline fail. First you need to create a Exceptionlogs table using following SQL script. CREATE TABLE [dbo].[ExceptionLogs]( [DataFactoryName] [varchar](100) NULL, [PipelineName] [varchar](100) NULL, [RunId] [varchar](100) NULL, [ErrorMessage] [varchar](1000) NULL, [CreatedOn] [datetime] NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[ExceptionLogs] ADD  CONSTRAINT [DF_CreatedOn]  DEFAULT (getdate()) FOR [CreatedOn] GO Also create a store procedure suppose you are going to use in ADF Pipeline. CREATE PROCEDURE [dbo].[Usp_ExceptionLog] (@DataFactoryName varchar(100), @PipelineName varchar(100), @runid varchar(100),@ErrorMessage varchar(1000)) AS BEGIN INSERT INTO ExceptionLogs ( [DataFactoryName], [PipelineName], [RunId], [ErrorMessage] ) VALUES ( @DataFactoryName, @PipelineName, @runid, @ErrorMessage ) END GO Here we are passing parameter like ADF name, Pipeline name, runid and error message that will mapped in ADF pipeline. Suppose you already created pipeline with some activities in ADF: Add a Stored Procedure activity to the canvas and connect another activity to this new activity using the arrow. When the connection has been made, right-click on the connection to change it to a Failure precedence constraint. This will change the color of the connector to red. Now click on Stored procedure activity and set the details like Stored procedure name and set the stored procedure parameters as seen in the below screen. Now we can run the pipeline and wait to see if failure of pipeline exception logs were stored in table. I hope this will help you.

Introduction: This blog will guide you through how you can schedule your Pipeline in ADF with the help of scheduled trigger. The Time is crucial when you schedule your Pipeline. Go through all the steps to avoid the common mistake which you might make. Step 1: Click on Trigger and select “New/Edit”. Step 2: Click on “New”. Step 3: Select Type = “Scheduled”. Set the Start Date (UTC) and Time Recurrence to 1 Week(s) and Select the required Day(s). Step 4: Click on OK and Publish the changes. Step 5: The Time that you must enter here is in UTC, so convert the local time at which you want to schedule to UTC and set it accordingly. Use the following link to convert it. https://www.prokerala.com/travel/timezones/time-converter.php

Introduction: ADF has a feature to Monitor and Audit the ADF activity. These Alerts can be Fired on both success and failure of a pipeline based on how we configure it. Step 1: Go to ADF Monitory and click “New Alert Rule” to create a new alert. Step 2: Set the Alert rule name and its severity: – Sev 0 = Critical – Sev 1 = Error – Sev 2 = Warning – Sev 3 = Informational – Sev 4 = Verbose Here we will select Sev1 Step 3: Set the Alert criteria as Failed pipeline runs metrics which will trigger only when a pipeline activity fails. Step 4: Set the Alert criteria as Failed pipeline runs metrics which will trigger only when a pipeline activity fails.   Step 5: Select the Name of the Pipeline for which you want to send the alerts. Step 6: Select All the Failure Types. Step 7: Set the Alert Logic to compare the metric value with threshold calculated based on time aggregation. Set the period and frequency based on which the above time aggregation in alert logic condition works. For now, keep these options as default and click on Add Criteria. Note: Only two criteria can be added. Check the Next Part 2 of this Blog at :

Introduction: ADF has a feature to Monitor and Audit the ADF activity. These Alerts can be Fired on both success and failure of a pipeline based on how we configure it. We have already created a Target Criteria in the previous in Blog. Check it out here: In this Part we will configure Email Notification for Failure of Pipeline Runs Step 1: Under configure Email/SMS/Push/Voice notification click on Configure Notification to set an action group. An action group defines a set of notification preferences and actions included by Azure alerts. Step 2: Select Create new. Give an Action Group name and Short name and click on add notification. Step 3: Give the Action Name and check mark the Email Option. Step 4: Add the Email address and click on Add notification. Step 5: You can see that your Notification is now added and you can click on Add action group. Step 6: You can click on Create Rule once your Target criteria and Notifications are added and Enable rule upon creation is enabled. Step 7: Thus a new Email Alert has been created for Failure of Pipeline.