Introduction: Handling Errors is a best practice in Integration when integrating to keep a track of the Errors that occurred during Integration. The Customized Error Handling is Handy to find quick Errors to understand them and resolve as required. This approach can be extended to any Source or Destination as required. Our Scenario: Integration from SalesForce – NAV. Different Approaches to Error Handling: No. Approach Description Customizations Required Comments 1. Entity Level All errors will be logged in an ‘Error Log’ entity in Salesforce and NAV along with the error description and date & time the error occurred. 1. Custom entity ‘Error Log’ 2. Fields for ‘Error Log’ entity: i. Name [String(100)] ii. From Entity [String(100)] iii. Error Message [Note] iv. Record ID [String(100)] v. Created On Date [DateTime]   For instance, i. Name: Error while creating ‘Customer’ in NAV ii. From Entity: Customer iii. Error Message: The specified Country is not valid iv. Record ID: Cust123 v. Created On Date: 08/07/2018   Example:    2. Field Level If an error occurs for a record during integration, the error description will be stored in a custom field on the record in Salesforce or NAV. A custom field ‘Error Description’ will have to be created on the form of all required entities. Field: Error Description: [Note]   For instance, when you open a Customer ‘Cust123’, Error Description: The specified Country is not valid   Example:

Phase 2/4: SSIS Integration Map for Invoice Header from SQL Server to Microsoft CRM: The Map after Completion will look as follows. Step 1: Add an OLEDB Source and configure it as follows: Click on Parameters and configure it as follows: Step 2: Add a derived column transformation to specify the Customer Type i.e 2 which indicates that Customer type is an Account. Also, we will map the Invoice Number to name, so we will create a copy of it. There is a single PriceList for all Invoices in my case so I have hardcoding for Lookup Purpose. Step 3: Add a CRM destination Block and configure it as follows: We will use Upsert Criteria specified on Invoice Number for Integration. The fields mapped are as following. Step 4: Add the remaining blocks for Custom Error Handling as explained in the Blog in Link: https://www.cloudfronts.com/creating-custom-error-log-table-in-ssis-for-oledb-block-and-kingwaysoft-dynamics-crm-block/ Note: This is the second part of the 4 part blog series. Part 1/4: Using FOR EACH Loop with KingswaySoft Connector for CRM Invoice Integration. Part 2/4: SSIS Integration Map for Invoice Header from SQL Server to Microsoft CRM. Part 3/4: SSIS Integration Map for Invoice Details from SQL Server to Microsoft CRM. Part 4/4: Delete Logic for Invoice in case of Incomplete Integration of Invoice details Logic.  

Part 3/4 SSIS Integration Map for Invoice Details from SQL Server to Microsoft CRM Step 1: Add a DFT to Integrate the Invoice Details in the Control Flow. The Map after Completion looks as follows: Step 2: Add an OLEDB Source and configure it as following: In our case we are Inner Joining Invoice and Invoice details Table based on OrderNumber and  Invoice Details and Products Table based on ProductID. This will help us to retrieve the columns which are not provided in a single Table and are required for Integration of Invoice Details. (Note: this might vary according to your scenario) Click on Parameters and configure it as follows: Step 3: Add a derived column transformation to create a unique name for Invoice Details as an upsert criteria. Step 4: Add a CRM destination Block and configure it as follows: We will use Upsert Criteria specified on Invoice Detail Unique Name for Integration of Invoice Details. The fields mapped are as following. Step 5: Add an OLE DB command transformation to update the Ported status to 1 indicating the Invoice details have been integrated. Configure it as follows: Step 6: Add the remaining blocks for Custom Error Handling as explained in the Blog in Link: https://www.cloudfronts.com/creating-custom-error-log-table-in-ssis-for-oledb-block-and-kingwaysoft-dynamics-crm-block/ Note: This is the third part of the 4 part blog series. Part 1/4: Using FOR EACH Loop with KingswaySoft Connector for CRM Invoice Integration. Part 2/4: SSIS Integration Map for Invoice Header from SQL Server to Microsoft CRM Part 3/4: SSIS Integration Map for Invoice Details from SQL Server to Microsoft CRM Part 4/4: Delete Logic for Invoice in case of Incomplete Integration of Invoice details Logic:

Part 4/4 Delete Logic for Invoice in case of Incomplete Integration of Invoice details Logic Introduction: To handle the scenario where the Invoice Details fail to get Integrated or Incomplete Integration of Invoice Lines the Invoice itself should get deleted from CRM as we don’t want to Integrate Incomplete records. Step 1: Add a DFT to Delete Invoice in the Control Flow as follows: The Map after Completion looks like the following: Step 2: In our case, we are Inner Joining Invoice and Invoice details Table based on OrderNumber and Invoice Details and Products Table based on ProductID. This will help us to retrieve the columns which are not provided in a single Table and are required for Integration of Invoice Details. Add an OLEDB Source block with the required query and configure it as follows: Click on Parameters to add a Parameter as follows: Step 3: We have a Ported field in our Data Base which set to 1 once Integrated with CRM. Add a Conditional Split Block to Split into two categories based on the sum of ported and count of ported as follows: Count of Ported = Sum of Ported This indicates that all the Invoice Header and all its Details have been Integrated Properly. In this case, we will add an OLE DB Command Block to update the status of Invoice Header to 1 as follows: Count of Ported != Sum of Ported This indicates that all the Invoice Header and all its Details have not been Integrated Properly. In this case, we will add an OLE DB Command Block to update the status of Invoice Header to 0 just as above where we update it to 1: Once the Status has been Updated we have to Delete the Faulty Invoice Record from CRM. We have performed Lookup based on Invoice Number as follows: Output: Source (SQL Server) Invoice Header- Invoice Details- Execution of Package – Destination (CRM) We can see that one Invoice is Integrated which had all proper data. The Other Invoice which failed to Integrate in some aspects has been automatically deleted from CRM. Note: This is the fourth part of the 4 part blog series. Part 1/4: Using FOR EACH Loop with KingswaySoft Connector for CRM Invoice Integration. Part 2/4: SSIS Integration Map for Invoice Header from SQL Server to Microsoft CRM Part 3/4: SSIS Integration Map for Invoice Details from SQL Server to Microsoft CRM Part 4/4: Delete Logic for Invoice in case of Incomplete Integration of Invoice details Logic

Customers are crucial to the success of any business. When you build your business focusing primarily on giving the best experience to your customer, your business is going to bloom. Your profits are going to multiply each year. Dynamics 365 for customer service is one of the best ERP solutions that you can find in the world.  It will help in streamlining the processes in all the departments to make your business customer-friendly and customer biased. When customers know that you value their association, they are going to stay with you for a long time becoming your loyal customers.  People who never used this ERP solution may not know how to find what they want. Here is a topic that you should learn as it will be quite helpful to you in the long run. Data Integration project support two types of executions Manual and auto-refresh i.e. schedule-based refresh. For a manual refresh, we have to select the project and click on run. In this blog, we will learn how to create a schedule to run an Integration project at a specific date and/or time. First, go to Admin Center Go to the Data Integration -> Projects and click on the ellipses of the project to be scheduled and click on Schedule We will get two options a. Run Manually b. Run on specific day and/or time  Now let’s schedule the project, in this example we going to schedule the project every hour starting at 17-Aug-2019 to 30-Aug-2019, and it should run form 12 AM to 2 AM. After scheduling click on save We can schedule the project in another way also, to do this  go to the scheduling after selecting on the Integration project After clicking on the project following window will open and then click on the Scheduling and same scheduling window will appear. In this way we can create schedule-based refresh for the Integration project

Error handling is very important part of any integration project, it is also very important to fix the issue when it is recognized, so it is very important to take action to resolve it earliest. In the Integration project of CDS we can setup email alert option in following way. First go to Admin Center Go to Data Integration -> Select the project -> Go to Scheduling Following window will appear Tick the checkbox and specify the email address on which we wanted to receive the alerts and click on save. In this way we can set up the email alert notification for Integration progress in CDS.

Introduction This log error will help us to understand and fix an issue as quickly. There are three main phases in an SSIS ETL execution life-cycle to catch errors: When data is being extracted from source systems When data is being transformed When data is loaded to the target systems Customized Error Handling also avoids the failure of Package during Runtime. It allows the package to be executed successfully and the Errors can be checked later from the Customized Error Log Table which you create to know what problem exactly occurred. Our Scenario For demonstration purpose, we will consider a Units Integration Map for Integration of Units from SQL to Dynamics CRM. In this Blog, we will Create a Customized Error Log Table in SQL and Catch the Errors from SQL (Source Block) and Dynamics CRM (Destination block). The Map for Unit Integration is as follows: Customized Error Handling in SSIS Execute the following query and create a customized Error Log Table in SSIS: CREATE TABLE [dbo].[ErrorLog]( [ErrorID][uniqueidentifier] NOT NULL default newid() primary key, [Entity] [varchar](250) NULL, [Record_Id] [int] NULL, [RecordName] [varchar](250) NULL, [ErrorDescription] [varchar](500) NULL, [DateTime] [datetime] NULL ) ON [PRIMARY] The structure of Table is as follows: ErrorID Entity Record_Id Record Name ErrorDescription DateTime Primary Key of Error Log Table. (System Generated) Unit UnitId Unit Name  Error Message  Error Log Date 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; //If an error occurred due to Check Constraint, the ErrorColumn would be 0, and that error affects the entire row. Hence there is no specific column for that error   if (Row.ErrorColumn == 0) { Row.ErrorDescription = “An error that affects the entire row”; }   //If an error occurred due to Data type, then errorcolumn name would be updated.   else { var componentMetaData130 = this.ComponentMetaData as IDTSComponentMetaData130; if (componentMetaData130 != null) { Row.ErrorDescription = componentMetaData130.GetIdentificationStringByID(Row.ErrorColumn).Replace (“Customer Target.Inputs[OLE DB Destination Input].Columns[“, “”).Replace(“]”, “”); } } }   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: Add an OLEDB destination block. Configure your OLEDB Connection Manager and Select the Error Log Table which you had created in SQL Server. In the Mapping section do the following Mappings and click on Ok. Error Handling at Dynamics Destination Block: Step 1: Perform the Steps 1 and 2 as specified above in Error Handling at OLEDB Source Block. Step 2: Configure the Script Component as following: In Input Columns section Select Error Code, Error Column and CrmErrorMessage. In Inputs and Outputs section Add following Columns Column Name Data Type Length ErrorLogDate database timestamp [DT_DBTIMESTAMP] —- Entity string [DT_STR] 50 In the Connection Managers Section add a new connection and select your CRM connection: In the Script Section click on Edit Script. You have to copy and paste the following Script inside the “public override void Input0_ProcessInputRow(Input0Buffer Row)” section. Code Snippet: Row.Entity = “Unit”; Row.ErrorLogDate = DateTime.Now;\ Click on Save and then Close the Window. Step 3: 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 CrmErrorMessage Column and select Data Type as String and length as 500 i.e. according to the length of columns SQL. Click on OK. Step 4: Add an OLEDB destination block. Configure your OLEDB Connection Manager and Select the Error Log Table which you had created in SQL Server. In the Mapping section do the following Mappings and click on Ok. Checking the Error Occurred during Integration You can see the Error rows passing through the Error Output and being logged in our Error Log Table. Open the Error Log Table to check the Errors Occurred. Now you can easily identify the errors occurred during Integration process from your Custom Error Log Table and solve them to have successful Integration results.  

Introduction This blog explains how to use “startswith” filter query in MS Flow. Use Case Using MS Flows to retrieve the bunch of students who’s mobile numbers begin with +91 and send them a text (using Twilio connector) regarding the Top Indian Universities. The “starts with” filter query in MS Flows allows to easily achieve this functionality. Steps to be Followed: We will use “Dynamics 365 – List records” Actions. Enter the Organization Name and select the entity. Filter Query: startswith(new_phonenumber,’+91′) “new_phonenumber” is the logical name of the field which stores the phone number of students. ‘+91’ is the value of phone number by which we want to filter the retrieved student list. Enter the above query in Filter Query: Run the flow to test.It will return all the students whose phone number starts with “+91”

Introduction This blog explains how to use “contains” filter query in MS Flow. Use Case Retrieve all the Accounts whose name contains ‘cloud’. Steps to be Followed: We will use “Dynamics 365 – List records” Actions. Enter the Organization Name and entity.Filter Query: contains(name,’cloud’) name is the logical name of the field which stores the account name. ‘cloud’ is the value of account name by which we want to filter the retrieved account list. Enter the above query in Filter Query: Run the flow to test.It will return all the account whose name contains “cloud’.

Microsoft has mandated TLS1.2 for Dynamics 364 v9.0, which is why we may face issue while connecting to the CRM online instance from visual studio for creating new report via fetch XML. This post will help developer to solve the connectivity issue for connecting with latest Dynamics 365 v9.0 using SQL Server Data Tools and FetchXml Authoring Extension. Issues: Not able to login Plugin registration tool (keeps popping up for Credentials)                     Not able to see custom entities in Plugin Registration tool Connection to CRM from Custom Web Application and Console fails Able to retrieve data in dataset in Report RDL but unable to preview           Root Cause: This issue occurs because of the latest update in the Microsoft TSL(Transport Layer Security) Protocol in SDK assemblies. Microsoft has allowed TSL connection 1.0 and 1.1 for the browsers or client to connect to the CRM org. The Microsoft TLS 1.0 implementation has no known security issues but because of the potential security vulnerabilities for future downgrade attacks and TSL vulnerabilities, Microsoft has discontinued the support for TLS 1.0 and 1.1 in Microsoft Office 365. Now Microsoft supports only TSL 1.2 or above. If you are connecting your org with the old version of plugin registration tool , then you may face this issue.   What is TSL ? Transport Layer Security (TLS) is a cryptographic protocol used to establish a secure communications channel between two systems. It is used to authenticate one or both systems, and protect the confidentiality and integrity of information that passes between systems.   HOW TO IDENTIFY: Use fiddler to check the your server request TSL Version. Install fiddler https://www.telerik.com/download/fiddler Go to Fiddler Setup -> Tools -> Options           Click on highlighted text after “Protocols” label and add ;tls1.2 text at the end and click “OK”:                               Run the report again. It should work as expected. This is how we can fix the connectivity issues between SSDT, Report Authoring Extension and Dynamics 365.