In this Blog we will see how we can create connection of D365 CRM using Kingwaysoft Connector. Prerequisite: Visual Studio 2015 or HigherKingswaysoft Tool InstalledD365 CRM Configuration: Register an app and client Secret.  Register an App in Azure and generate client Secrect  Go to Azure portal>Register an App Specify Name and Redirect URL and click on URL, generate Client Secret if required. We will use this while creating CRM connection using KingswaySoft. Creating CRM Connection Open SSIS > Go to Connection Manager > Add Connection and following window will appear, click on Dynamic CRM and Click add. Now it will ask for CRM details, Click on Test Connection to check connectivity. Hope this helps!

In this blog we will see how we can connect our shopify store to SSIS using kingsway soft connector. Step 1: Open your shopify store instance and note down your store name somewhere we will need that while creating a connection. Step 2: Open Apps from the left hand side menu Step 3: Scroll down to bottom and click on manage private apps Step 4: Copy the API key generated in the API Key section. If now app is created create one by clicking on create new private app. Step 5: Open SSIS and create new project. Download the kingsway soft connector from https://www.kingswaysoft.com/products/ssis-integration-toolkit-for-microsoft-dynamics-365/download Step 6: Once you have installed the connector in SSIS create connection in SSIS by selecting REST as source Step 6: Select Shopify from the service dropdown Step 7: Enter the shop name that you copied at the beginning. Select the API Key authentication mode Step 8: Add your API key and password and test the connection. Check for spaces while copying the API key. In this way you can connect your Shopify store to SSIS. Hope this helps

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.

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 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

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: In this blog, I will demonstrate how to remove or filter duplicate records in SSIS using Sort Tranformation. Steps: First, configure you OLE DB as your Source using the OLE DB Source as shown below. Bring the Sort Transformation and configure it as shown below. Check the box at the bottom of the Window that says ‘Remove rows with duplicate sort values’. Now you can connect to your destination by dragging the required block and connecting the Sort Transformation to the Destination block. For blog purposes, I have connected it to the Multicast block. Your Data Flow should look like: Click on start to execute your Data Flow. Benefits: Removal of duplicate rows can be useful when trying to avoid Data Redundancy in your System as well as to reduce the memory required.  

Introduction: We can split an Address column into multiple columns for Flat Number, Building, Road, City etc. in order to make it easier to process the data. In order to do this, we can use the Derived Column Transformation in SSIS. Steps: In the screenshot below, you can see the Address column has an aggregated address for each employee. In your SSIS Package,  bring a Data Flow Task. Configure you Excel Source in Connection Manager and select the sheet you want the data from. Check in the Columns Tab to verify if the Columns are correct. Now drag the  Derived Column Transformation and connect the Excel Source to it. Double click the Derived Column Block and configure it as shown in the screenshot below. Flat: SUBSTRING([Col 2],1,FINDSTRING([Col 2],”,”,1) – 1) Building: SUBSTRING([Col 2],FINDSTRING([Col 2],”,”,1) + 1,((FINDSTRING([Col 2],”,”,2) – FINDSTRING([Col 2],”,”,1)) – 1)) Road: SUBSTRING([Col 2],FINDSTRING([Col 2],”,”,2) + 1,((FINDSTRING([Col 2],”,”,3) – FINDSTRING([Col 2],”,”,2)) – 1)) Area: SUBSTRING([Col 2],FINDSTRING([Col 2],”,”,3) + 1,((FINDSTRING([Col 2],”,”,4) – FINDSTRING([Col 2],”,”,3)) – 1)) City: SUBSTRING([Col 2],FINDSTRING([Col 2],”,”,4) + 1,((FINDSTRING([Col 2],”,”,5) – FINDSTRING([Col 2],”,”,4)) – 1)) PIN: SUBSTRING([Col 2],FINDSTRING([Col 2],”,”,5) + 1,((LEN([Col 2]) – FINDSTRING([Col 2],”,”,5)))) Once the configuration is done, connect Derived Column Transformation to you Target Block. Configure your Target Block to your Destination as shown below and check the Mappings to ensure the correct Source and Target columns are mapped. This is what your Data Flow should look like: Click on ‘Start’ when done. Once the execution is complete, check your Target Database for the desired output. Conclusion: This can be a requirement in many scenarios and by following the steps mentioned above, we can achieve the desired output.

In this Blog we will walk-through the migration of Data from Microsoft Dynamics CRM (Source) to Microsoft Dynamics CRM (Target) 2015 using SSIS. We would be using SSIS Integration Toolkit for Microsoft Dynamic CRM by Kingsway Soft. We would be referring SSIS Integration Toolkit for Microsoft Dynamics CRM by Kingsway Soft as Kingsway-SSIS Toolkit. This toolkit provides 4 major components for Integration and migration of Data. Microsoft Dynamics CRM Connection Manager Microsoft Dynamics CRM Source Component Microsoft Dynamics CRM Destination Component Microsoft Dynamics CRM OptionSet Mapping Component Pre-Requites: This Toolkit is supported on below versions of Microsoft Dynamics CRM and Microsoft SQL server. .NET Framework 3.5 SP1 Microsoft Dynamics CRM Microsoft Dynamics CRM Online Microsoft Dynamics CRM Server 2015 Microsoft Dynamics CRM Server 2013 Microsoft Dynamics CRM Server 2011 Microsoft Dynamics CRM Server 4.0 Microsoft Dynamics CRM Server 3.0 SQL Server Microsoft SQL Server 2014 (since v5.0) Microsoft SQL Server 2012 Microsoft SQL Server 2008 R2 Microsoft SQL Server 2008 Microsoft SQL Server 2005 Other Tools that provide the same integration as Kingsway Soft are as below: Cozyroc.com http://cozyroc.com/ssis/dynamics-crm-source http://cozyroc.com/ssis/dynamics-crm-destination PragmaticWorks.com https://pragmaticworks.com/Products/Task%20Factory/Features/DynamicsCRMSource https://pragmaticworks.com/Products/Task%20Factory/Features/DynamicsCRMDestination Work Flow: After Installation of Toolkit, below options are visible in SSIS Toolbox in SSDT (SQL Server Data Tools). A new connection can be added in Connection Manager as below for DynamicsCRM. Information needs to be provided to connect to CRM server as below. (http://www.kingswaysoft.com/products/ssis-integration-toolkit-for-microsoft-dynamics-crm/help-manual) The Authentication Type option allows us to specify the type of CRM server (deployment) and what authentication is used. There are four options available. Active Directory (On-Premise) Federation (IFD, On-Premise or Partner-hosted) Online Federation (Office 365 CRM Online) LiveId (Legacy CRM Online) The CRM discovery server can be found in the CRM system from Settings → Customizations → Developer Resources after logging in to your CRM application. Service endpoint gets auto populated. You can specify the Timeout in seconds. Default is 2 minutes. On providing the User Name and Password, Organization gets populated in drop down list. Select the Organization name and test the connection. Once the connection is successful. Click Ok. If you connectivity is through Proxy server you can specify the connection details for the same in the second tab Advanced Settings. It is recommended to have the box ticked-on for Retry on Intermittent Errors. Follow the same steps as above for creating Destination connection for Target CRM where the data is to be migrated. Launch SSDT (SQL Server Data Tools and select Integration Project and drag and drop the Source and the Target. Here in this example we have not performed any data or file transformations. It’s simply connecting to CRM source and pushing the data to Target CRM based on mappings specified for entities as well as Text Lookups specified if any. In this example we are migrating Entity – Accounts. CRM source Connection name in this example is named as “Source” and CRM Destination connection as “Target”. Using this toolkit, in the columns Tab, Most of the columns gets auto-mapped.  Verify the mappings, specifically the ones with Text Lookup. Action gives the options like Create, Update, Delete, Upsert, Merge, Convert and Execute Workflow. We need to select Create if we are simply migrating the data into target CRM (History Load). Select the Destination entity. Upsert Settings are applicable only if Upsert Action is selected. Choose Target Fields. Target Entity gets auto populated. Using the drop down list select Target text/ Integer Field. Advanced Options can be left unselected. Cache strategy needs to be selected with Performance and size of data that needs to be migrated. (This is similar to Cache mode in SSIS look-up transforms). Now the package is ready to be executed. Execute the package and verify by logging into CRM, all the fields are populated. Note: As a general practise during SSIS package creation, sequence containers are used for each entities that are to be migrated. Only one source and destination CRM connection needs to be created. Benefits of Parallelism can be used for performance benefits as well as individual tasks (for single entity) can be selected in the data flow to be executed, with others as disabled.