Join Our Team
Contact
Insights SSIS Integration with Dynamics CRM
Insights

SSIS Integration with Dynamics CRM

SQL Server Integration Services is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks. SSIS is a platform for data integration and workflow applications. It features a data warehousing tool used for data extraction, transformation, and loading. 

Pre-requisites:  

  1. Visual Studio 2017 Professional from an MSDN(Microsoft Developer Network) subscription 
  2. SQL Server Data Tools(SSDT for 2017) : https://go.microsoft.com/fwlink/?linkid=2086373 
  3. Kingswaysoft : https://www.kingswaysoft.com/products/ssis-integration-toolkit-for-microsoft-dynamics-crm (Download 64 bit version) 

Download the above software in order as they are dependent on each other. 

Steps to be followed to create a package: 

  1. Open Visual Studio 2017 (SSDT). 

Note: Using Visual Studio with SSDT allows you to use the SQL Server Object Explorer to visually explore tables, views, stored procedures, and many more objects in your SQL Data Warehouse as well as run queries. 

  1. Create a new project: Click File –> New –> Project. 
  2. Navigate to Business Intelligence –> Integration Services –> Integration Service Project and give project name. 

 Make sure that SSIS toolbox is present in Visual Studio as shown below: 

If SSIS Toolbox not visible in SSDL, then follow the steps shown in the link: https://www.mssqltips.com/sqlservertip/3615/ssis-toolbox-is-not-visible-in-sql-server-data-tools/ 

The next step is to create a connection. Go to control flow–>  There is an option for connection manager below. Right click to create new connection. Click on DynamicCRM and click Add. 

Authentication and server URLs for Creating a connection: 

  1. Server Endpoint: SOAP 2011 
  2. Authentication type: Online Federation 
  3. CRM Discovery Server: North America 
  4. Provide Username and Password. 
  5. Organization: Select the environment where you want to run this package.  
  6. Click Test connection and if it is successful then click ok. 
  • After creating the connection we need to create Dynamic CRM Source and Dynamic CRM Destination. We will insert our FetchXML query or other details at Source and perform necessary operations. 
  • Click on Data Flow–>Select Dynamic CRM Source and Dynamic CRM Destination from SSIS Toolbox. Drag the arrow in between the source and destination 
  • Double click CRM Source–> Select the connection we created under connection manager(example of using FetchXML in the below fig). 

Batch size comes out to be 2000 automatically. Select source type as: 

  • Entity –> select entity name where you want to perform the operation. 
  • FetchXML –> Select FetchXML as option and then copy the code below. You can verify the count from XRMToolbox(View Record Count). 
  • Go to columns and select the Unique Identifier column mentioned. 
  • Double click Destination–> Select connection and the Action which you want to perform such as Create, Update, Delete etc. And select the destination entity which is basically the entity name. Click Ok. 
  • Package successfully created. Click Save button. 
  • On the right hand side, go to solution explorer–> SSIS packages –> Package.dtsx (package we created)–> Execute package. 

Reference: