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.

Introduction: This blog will show you how you can color individual rows differently based on different conditions and the row headers in matrix (Not alternating rows). Our Scenario: I want to apply colors to different rows of the following Buckets: Current – No Color 1-30 Days Past Due – Yellow 31-60 Days Past Due – Orange 61-90 Days Past Due – Red 91 or More Days Past Due – Red Step 1: Create a new calculated column in your data source which applies a numeric value to each header type that you would like to have highlighted. We have created a Calculated Column using the following query. Step 2: Select the Matrix to which you want to apply the formatting and go to conditional formatting section in the Format Tab and turn the Background Color Option “On”. Step 3: The Conditional Formatting is applied for different fields in the Values section in the Matrix. So we will apply conditional formatting according to No.  field first. Select Format by “Rules”. In Based on field select “Sum of Color Column” and in Summarization select “Sum”. In the Rules section add the Rule as shown in the Screenshot.  Step 4: Apply the other rules for different colors same as above. Step 5: The Colors have been applied to different buckets according to our rule for “No.” Column. Step 6: Repeat the same steps by selecting different fields from the drop down under Conditional Formatting, one by one. Step 7: Thus we have colored the different rows of the Matrix successfully based on our condition.

Our Scenario: To resolve the Errors that occur due to  componentMetadata130 and to capture the Error Log Date and Error Description while doing custom Error Handling in SSIS use the following steps. Solution: Error Handling at OLEDB Source Block: Step 1: Add a Script Component to catch the Error Description, Error Log Date and Entity. Select Transformation and click on Ok. Step 2: Connect the Error Output (Red Arrow) from OLDEB Source to the Script Component. Select “Redirect row” for all columns in the Error and Truncation Columns to redirect Error Output to Script Component. Step 3: Configure the Script Component as following: In Input Columns section Select Error Code and Error Column. In Inputs and Outputs section Add following Columns Column Name Data Type Length ErrorDescription Unicode string [DT_WSTR] 500 ErrorLogDate database timestamp [DT_DBTIMESTAMP] —- Entity string [DT_STR] 50 In the Connection Managers Section add a new connection and select your SQL connection. In the Script Section click on Edit Script. After a minute a New Editor Window will Open. Here you have to copy and paste the following Script inside the “public override void Input0_ProcessInputRow(Input0Buffer Row)” section. Code Snippet: try             {                 Row.Entity = “Unit”;   Row.ErrorDescription =      this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);                 Row.ErrorLogDate = DateTime.Now;             }             catch (Exception ex)             {                 Row.ErrorDescription = “Unable to get Error Column Name”;             } Click on Save and then Close the Window. Step 4: Add a Data Conversion Block to avoid any Truncation Errors because of Data Type Conversion between NVarchar and Varchar Data Types of the Error Description Column. Select ErrorDecription Column and select Data Type as String. Click on OK. Step 5: Connect the Output columns of Script Component to the required Destination Columns. Conclusion: This is the simplest way in which we can record the Error Description and Error Log Date while custom Error Handling in SSIS.

Scenario : We are building a SSIS Package but receiving the “The Locale ID 9 is not installed on this system” error.  We are using the SQL server 2017 Enterprise Edition and Visual Studio 2015 Community Edition with SSDT 2015 Edition. Solution : Step 1: Go to the Project Properties as shown below. Step 2: In the Configuration Properties check if the Target SQL version is set to correct version. Step 3: If still not working set the SQL Server to a lower version than your current version.

Introduction: Creating a Connection is one of the first Steps during the integration of data. While performing Integration with Dynamics AX or more specifically Dynamics 365 for Finance and Operations create a connection using the following steps. Step 1: Right Click on Connection Managers and click on New Connection Manager. Step 2: Configure your AX Application to get the following details: Step 2.1 Go to www.portal.azure.com and login with your credentials. Go to “app registrations ”. Step 2.2 Enter a Name for the Application, appropriate Support Account Type and Redirect URI having a Similar Address as that of the Service URL which you Enter and finally click on Register. Step 2.3 Note Down the Following details for creating connection. Step 2.4 Click on New client Secret and add description and select expires as required. Step 2.5 Add description and select Never in expires section and click on Add. Step 2.6 This is the Client secret which is Generated only once, so must copy and store it in a file for future reference. Step 2.7 In the Data Scope section select required value as per your AX deployment and click on register and click on Open in Browser.   You will be redirected to your Finance and Operations page. Once authorized you can test the Connection by clicking on the Test Connection Button. Conclusion Now you can Move on with development of your Control and Data flow for Integration with Finance and Operations as a Source/Destination.

Introduction: Creating a Connection is one of the first Steps during integration of data. While performing Integration with AX or more specifically Dynamics 365 for Finance and Operations create a connection using the following steps. Step 1: Right Click on Connection Managers and click on New Connection Manager. Choose the Dynamics CRM option. Step 2: Choose the SOAP 2011 Option for Service Endpoint option. Step 3: Choose the Online Federation for Authentication Type option. Step 4: In the CRM Discovery Option copy the endpoint address from Settings > Customizations > Developer Resources > Copy Endpoint Address. Step 5: Paste this in CRM Discovery Section. Enter the Username and Password of your CRM account. Step 6: Choose the required organization from the dropdown. Step 7: Click on Test Connection and then OK after successful testing. Conclusion: Thus we have successfully created a connection with Dynamics CRM and we can now Integrate data using it a source /destination.

Introduction If have a parameter on our report to see the past “N” days data on our report, we can perform the following steps. Step 1: Add a new Parameter, say “Days” Step 2: Under the Available Values Section, Add a new Value for the Parameter with value as 7 and expression having the following formula: =DateAdd(“d”,-7,Globals!ExecutionTime) Step 3: Under the Default Values Section, Add a new Value for the Parameter with expression having the following formula: =DateAdd(“d”,-7,Globals!ExecutionTime) Step 3: In your Dataset, add the following Filter Condition in the XML. <filter type=”and”> <condition attribute =”modifiedon”  operator=”on-or-after” value=”@Days” /> </filter> Conclusion Thus, now you can view your report with records modified in the past “N” days.

Introduction: Use the Update Block to update existing records in the source or target datastore. While integrating with Microsoft Dynamics NAV as a destination use the Update Merge Block to perform the Update Operation in Microsoft Dynamics NAV. The Update Merge block can configured as follows. General Tab Following Options are available under the General Tab. Change the Block Label Add Description Set Batch Processing Options Select the entity to use for this Operation. Matching Criteria Tab Following Options are available under the Matching Tab. Specify one or more fields in your source and target data to select records for this operation. Fields Tab Following Options are available under the Fields Tab. Map the required fields from your source to destination directly or based on some formulae. Note: The following fields must Hardcoded Error Handling Tab Following Options are available under the Error Handling Tab. When an operation fails — If the record cannot be updated, an error is logged. If no matches are found — If the record being processed does not match the criteria in the Matching Criteria Tab, it is not updated, and an error is logged. Errors and Warnings Tab Following Options are available under the Errors And Warnings Tab. Various Errors available are displayed. Once you have corrected the errors click on Validate button to update the Errors and Warnings Tab.