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.

Introduction: Establishing a connection is an important step before developing a solution for Integration. While integrating with Microsoft Dynamics NAV as a source/destination a Connection is needed which can be established as follows. Step 1: Click on “More” tab and choose Connections. Step 2: Click on “Addition” Symbol to add a Connection. Step 3: Install On-Prem Agent on Nav server. Refer to the link below. https://help.scribesoft.com/scribe/en/index.htm#sol/agent/agentinstall.htm?Highlight=On%20prem%20agent Step 4: Add the details as follows. Connector Type: Microsoft Dynamics 365 Business Central/NAV. Name: Give a name for your connection. OData Service URL: Get the required Odata URL from NAV. Note: The URL should end with “Odata” at end. No additional forward slash must be added at the end of the URL which might result in errors. http://ftl-nav-dev:7048/FTL-DEVInstance/OData User: Find it after the date as follows. Password: Password for NAV Company Name: Find it before the date as follows. Agent: Select the On-Prem Agent which you had installed.

Introduction: Converting Date/Time values according to a time zone is quite challenging task in SSRS Reporting. For D365 CRM online we can achieve this using CDate Function. I will demonstrate how to use CDate function with a dynamic time-zone parameter. Step 1: Add this reference to the report properties: Click on “References” and then click on “Add” button under “Add or remove assemblies”. Browse the following file from your BIDS folder. Microsoft.Crm.Reporting.RdlHelper, Version=9.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35 Click on Ok button to save the changes. Step 2: Create the parameter in the report data section in the “Parameters” folder as shown below: In the “Default Values” section check the click on “Add” Button. Enter the User Time Zone in the Formula and click on OK to Save the Changes. Step 3: To use this functionality is simply as follows: DateValue(CDate(Microsoft.Crm.Reporting.RdlHelper.DateTimeUtility.ConvertUtcToLocalTime(Fields!msdyn_dateValue.Value, Parameters!CRM_UserTimeZoneName.Value))) Example: If the Expression or Formula is as follow: Last 30 =WeekdayName(Weekday(DateSerial(Year(Fields!msdyn_dateValue.Value), Month(Fields!msdyn_dateValue.Value),”1″).AddMonths(1).AddDays(-30))) The Replace it with: Last 30= WeekdayName(Weekday(DateSerial(Year(DateValue(CDate(Microsoft.Crm.Reporting.RdlHelper.DateTimeUtility.ConvertUtcToLocalTime(Fields!msdyn_dateValue.Value, Parameters!CRM_UserTimeZoneName.Value)))), Month(DateValue(CDate(Microsoft.Crm.Reporting.RdlHelper.DateTimeUtility.ConvertUtcToLocalTime(Fields!msdyn_dateValue.Value, Parameters!CRM_UserTimeZoneName.Value)))),”1″).AddMonths(1).AddDays(-30))) Note: This is an easy way of dynamically converting a time zone from UTC to the user’s local time using CRM Online. We have to replace all the formulae which contain the DateTime field value which is retrieved directly from FetchXML with the above mentioned Function to avoid issues in SSRS reports due to Time Zone Conversion.

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.in/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.in/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

Let’s say you need to create a new store with multiple registers on CPOS.   It is now possible to have a retail store with multiple registers each running CPOS and each having their own dedicated hardware.   For example: A store with two registers both running CPOS and both having their own receipt printer, scanner, etc. . Here comes the concept of a shared hardware station for CPOS, It may seem impractical to have multiple registers sharing the same scanner, pin pad, etc but it is actually possible.   You’ll need to install a hardware station per CPOS and then you should be good to go.   Sharing hardware can be handy in scenarios with limited space or lesser sales persons moving around taking the sales and so on and it’s valid for CPOS.   Note: However, for MPOS you need a dedicated hardware.   Please notice that you can’t have multiple hardware stations installed on the same machine. Please do correct me anyone if that has changed 🙂 This can be done absolutely and is perfectly possible. Just deploy a Hardware station on each device and it will work as discussed in the example above.

Microsoft Dynamics Nav Integration is one of the most popular ERP solutions that most of the medium and large enterprises use today. It is an ERP solution that is not only easy to learn but is easy to learn and implement. Your staff is going to become a lot more efficient when they use this ERP solution.  Microsoft is one of the best companies in the world for all the right reasons. This software contains some of the best and unique features that will not only raise the productivity of the staff but will also improve your performance. But, if enterprises want to enjoy the benefits that come from this ERP solution, they need to plan on upgrading Dynamics NAV to a cloud-based environment. Introduction: Working with failed Scheduled Jobs can be a tiresome process. This is exactly the case where one of my clients has an Invoicing & Payment Jobs which run once a day and Job Queue Entries fail to trigger these Jobs. No Errors in the Logs, No Execution Entry, Simply No Way of Figuring it out as to why Job didn’t trigger. Contacting Microsoft Support, they are asking to simply Upgrade the Application to the latest version. Which is not a preferred option as the database size is around 200GB. Let’s see how to resolve this in a smarter way. Pre-requisites: Microsoft Windows Task Scheduler Microsoft Windows Powershell Microsoft Dynamics NAV(Administration Module for PowerShell & Development Environment) Books & References: https://docs.microsoft.com/en-us/powershell/module/microsoft.dynamics.nav.management/?view=businesscentral-ps Demonstration: 1. Building Objects to Run through a Codeunit: As you know that Job Queue Entries support Reports and Codeunits and NAV Administration Module only supports Codeunit. So, in order to make it work, create a Codeunit to execute all the Reports and code units. 2. Creation of PowerShell Script: This PowerShell Script executes with the help of NAV Administration to Run the Codeunit. Prior to running the script, you need to set the Execution Policy. Syntax: Invoke-NAVCodeunit <Codeunit No.> – ServerIntance <ServerInstanceName> -CompanyName  <Company Name> -Force 3. Setting Up Task Scheduler Triggers: By Setting up the Triggers, you can decide recurrence to execute the Codeunit. It can be Hours, Daily, Weekly, Monthly, One-Time or Infinite. You can also set the time for precise execution. 4. Setting Up Task Scheduler Commands: Whenever we execute the PowerShell Script through CMD, we run the commands as follows. Command: PowerShell.exe <PS Script File Path> Here, we pass the PowerShell.exe into the script and PS Script File Path as a Parameter. Conclusion: Thus, using NAV Administration Module, Power-Shell and Task Scheduler I was about to replicate Job Queue Entries functionality. Although this works perfectly for NAV and Business Central – On-Premise, it works for Business Central on SaaS. In my upcoming blog, I’ll also show we can achieve this. Hope you guys find this blog useful 😇

“Run Jobs” – as the name suggests is a process between Retail Channels and Retail essentials through which we transfer data between channels and database. Please Note: Jobs Run at a specified time which may be different for every Retail Scheduler. Be aware of what the time is set for jobs to run automatically.  Running jobs at a specific time requires setting to be done so that we can schedule a specific job to run at specific time. It can be easily done manually. You’ll have to follow the below steps: Step 1: Go to distribution schedule or Channel database in Retail Dynamics 365. If the job doesn’t run from distribution schedule run it through channel database. Step 2: Go to download sessions and make sure your Job is available or in applied state. If it is in available state, go to “Batch Jobs“. Step 3: Now you will have to find the same batch job that you were searching and need to run it at a specified time. Step 4: Here, since the Job is in waiting state, you need to change it’s scheduled date and time to current time. This change must be according to the time at which you want your job needs to be run. The specified time will make your job run immediately.   Step 5: You can check if Job is being applied in “Download sessions” whenever required. Step 6: You can also change how often the job should run from going to Recurrence. Step 7: Only things highlighted in Red boxes should be checked. Hope this helps!!! Thank you! Please feel free to post any doubts you have.

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.