Contents
- Installation
- Setup a CRM Connection using CRM Connection Manager
- Add CRM Components to SSIS Toolbox
- Use of CRM Source Component
- Use of CRM Destination Component
- License Manager
Installation
To install and run SSIS Integration Toolkit for Microsoft Dynamics CRM, your system needs to have the following components installed.
When you have confirmed that your system satisfies the above prerequisites, you can navigate to KingswaySoft website at http://www.kingswaysoft.com to download the installation package. In the download page, you will find two download links for x86 and x64 system respectively, make sure to choose and download the right bit for your system.
After you have downloaded the package, you can install the software by following the instructions on screen.
Setup a CRM Connection using CRM Connection Manager
SSIS Integration Toolkit CRM Connection Manager is an SSIS connection manager that can be used to establish connections with Microsoft Dynamics CRM Server.
CRM connection manager allows you to specify how you want to connect to your CRM server.
To add a CRM connection to your SSIS package, you need to right-click in Connection Manager area, and choose "New Connection..." from the context menu. You will be prompted the following window, where you can choose "DynamicsCRM" item to add the connection.
- Authentication Type
-
The Authentication Type option basically allows you to specify the deployment type of your CRM server and what authentication is used. There are three option available.
- Active Directory (On-Premise)
- Federated (On-Premise, Partner-hosted or Online Federation)
- LiveId (CRM Online)
- CRM Discovery Server
-
CRM Discovery Server option allows you to specify how to discover your CRM organizations and their web service URLs. The CRM discovery server can be found in CRM system from Settings | Customizations | Developer Resources after you have logged in to the CRM server.
Please ensure to enter the full URL of your CRM discovery server, the path after the server name is not needed.
For instance, the following is the list of the CRM discovery server that you can enter in CRM connection manager, if you are using CRM online or Office 365 (The list is just for reference only, and it may not represent the most updated information).
Location Discovery Web Server URL Identity Provider North America https://dev.crm.dynamics.com/ Windows Live ID https://disco.crm.dynamics.com/ Microsoft Office 365 EMEA https://dev.crm4.dynamics.com/ Windows Live ID https://disco.crm4.dynamics.com/ Microsoft Office 365 APAC https://dev.crm5.dynamics.com/ Windows Live ID https://disco.crm5.dynamics.com/ Microsoft Office 365 Similarly, for On-Premise or IFD deployment, the URL should typically be in one of the following formats, depending on your CRM server's setup.
- http://CrmServerName/
- https://CrmServerName/
- http://CrmServerName:PortNumber/
- https://CrmServerName:PortNumber/
- Service Endpoint
-
The Service Endpoint option allows you to specify the service endpoint that you want to use in order to connect to Microsoft Dynamics CRM server. We currently support the following two options.
- SOAP 2011 (CRM 2011)
- SOAP 2007 (CRM 4.0 and CRM 2011)
Apparently, if your server is CRM 4.0, you will only be able to use SOAP 2007 endpoint.
- Use Integrated Authentication
-
This option is only available if you have chosen "Active Directory (On-Premise)" as Authentication Type. Using this option, you have the flexibility of not saving your login credential in the SSIS package, which might help simplify the deployment process of your SSIS packages.
- User Name
-
User Name option allows you to specify the user account that you want to use to connect to CRM server. Depending on how the CRM connection is used, the user needs to have proper privileges in CRM system .
- Password
-
Password option allows you to specify the password for the above user in order to login to CRM server.
- Domain
-
Domain option is used to specify the active directory domain of the CRM user. This option is only available when the Authentication Type is "Active Directory (On-Premise)".
- Organization
-
After the CRM server and login credential information have been provided, you can click the drop down button of the Organization option to show the list of available organization that the user has access to. Select the organization that is intended to be used.
- Test Connection
-
After all the connection information has been provided, you may click "Test Connection" button to test if the user can successfully login to the CRM server.
Add CRM Components to SSIS Toolbox
SSIS Integration Toolkit for Microsoft Dynamics CRM includes two data flow components, they need to be added to SSIS toolbox before you can use them in SSIS data flow task.
NOTE: If you are using SQL Server 2012 development environment, you don't need to do so, as SQL Server 2012 automatically lists all available pipeline components by scanning the system.
To do so, you need to create a new data flow if you don't have one yet, and switch to SSIS data flow page. Then you may right-click on the toolbox area to bring up the context menu, where you select "Choose Items..." option as shown below.
You would now be presented with a window called "Choose Toolbox Items", switch to "SSIS Data Flow Items" tab, and find Dynamics CRM Destination and Dynamics CRM Source components from the list, and make sure to check both of them.
You would now be able to use the two components by dragging and dropping either of them to the design surface of your SSIS data flow task.
Use of CRM Source Component
CRM Source Component is an SSIS data flow pipeline component that can be used to read / retrieve data from Microsoft Dynamics CRM server. The CRM source component supports using either a CRM entity, or a FetchXML query as the data source.
CRM Source Component includes the following two pages to configure how you want to read data from Microsoft Dynamics CRM.
- General
- Columns
The General page allows you to specify the general settings of the CRM source component, while the Columns page shows you all available columns from the entity or FetchXML query.
General page
The General page of CRM Source Component allows you to specify the general settings of the component.
- CRM Connection Manager
-
CRM source component requires a CRM connection in order to be able to connect to Microsoft Dynamics CRM server. The CRM Connection Manager option will show all DynamicsCRM connection managers that have been created in the current SSIS package.
- Batch Size
-
Batch Size option allows you to specify how many records you want to retrieve each time. The default value is 2000. The maximum allowed value is 5000. This parameter should not be too small for performance reason.
- Source Type
-
Source Type option allows you to specify whether you want to read data from CRM entity, or use a snippet of FetchXML query. Using Entity option, you have the flexibility of visually picking which fields that you want to read from CRM entity. When FetchXML query is used, you can take advantage of the power and flexibility of CRM FetchXML query, such as JOIN, filtering criteria, etc. Which option to choose is really up to your business requirements.
- Source Entity
-
Source Entity option is only available when Source Type option has been chosen as Entity. When the option is clicked, SSIS Integration Toolkit will retrieve a list of all available CRM entities for the selected CRM connection. Please note that the list will only include the entities that the specified user in the CRM connection manager has read privileges.
- FetchXML
-
When the above Source Type option has been chosen as FetchXML, you will be presented a text editor that allows you to enter a snippet of FetchXML query, which is used to retrieve data from Microsoft Dynamics CRM server.
-
A big advantage of FetchXML option over Entity is, you can use FetchXML to build complex query that retrieves data from CRM. You can use JOINs in the FetchXML query, so that you read data from multiple entities, also you can apply filtering criteria in order for it to only bring back the data that meets the criteria, so that you work with the only data that you are interested in.
Columns page
The Columns page of CRM Source Component shows you all available columns from the entity or FetchXML query that you have defined in General page.
When the Source Type option has been chosen as Entity in the above General page, you will notice that the grid in Columns page has a checkbox column on the left which allows you to specify what fields you want to read from the chosen CRM entity.
On the top left of the grid, you can see a checkbox, which can be used to toggle the selection of all available CRM fields. This is a productive way to check or uncheck all available CRM fields.
NOTE: As a general best practice, you should only select the CRM fields that are needed for the downstream pipeline components, this would not only speed up CRM queries, but also save resources for SSIS engine.
When the Source Type option has been chosen as FetchXML in the General page, you will not see any checkboxes in the grid. The reason is, FetchXML query itself defines what to read from CRM server.
Use of CRM Destination Component
CRM Destination Component is an SSIS data flow pipeline component that can be used to write data to Microsoft Dynamics CRM server. You may create, update, delete, or upsert (Update / Insert) CRM records using the CRM Destination Component.
CRM Destination Component includes the following three pages to configure how you want to write data to Microsoft Dynamics CRM server.
- General
- Columns
- Error Handling
The General page is used to specify the general settings of the CRM destination component, Columns page allows you to map the columns from upstream components to CRM fields in the destination entity, and Error Handling page allows you to specify how errors should be handled when they happen.
General page
The General page of CRM Destination Component allows you to specify the general settings of the component.
- CRM Connection Manager
-
CRM destination component requires a CRM connection in order to be able to connect to Microsoft Dynamics CRM server. The CRM Connection Manager option will show all DynamicsCRM connection managers that have been created in the current SSIS package.
- Action
-
Action option allows you to specify how the data should be written to Microsoft Dynamics CRM server. There are 4 action types available for this option.
- Create - Create new record(s) in CRM
- Update - Update existing record(s) in CRM
- Delete - Delete record(s) from CRM
- Upsert - Update any existing record(s) in CRM if matching can be found, otherwise create a new record with the information from upstream pipeline components.
- Destination Entity
-
Destination Entity option allows you to specify which CRM entity to write the data. When the option is clicked, SSIS Integration Toolkit will retrieve a list of all available CRM entities for the selected CRM connection. Please note that the list will only include the entities that the specified user in the CRM connection manager has the proper write privileges.
NOTE: You may select a N:M (many-to-many) CRM relationship entity as the Destination Entity, in which case you use Create action to create association between two CRM records, and use Delete to disassociate two records. When Create action is used for a N:M relationship entity, the component will first try to find out if an association already exists in the system, it will only create new association if there is no existing relationship between the two CRM records.
- Remove Unresolvable References
-
Remove Unresolvable References option specifies how to handle CRM lookup fields when the reference records are not available. When the option is checked, if a CRM lookup field refers to a CRM record that doesn't exist in the system, the CRM lookup field will be removed before the data is written to the CRM system.
NOTE: Remove Unresolvable References option is not available when the Action is Delete.
- Enable CRM Duplicate Detection
-
Enable CRM Duplicate Detection option specifies whether CRM duplicate detection should be fired when writing data to Microsoft Dynamics CRM.
NOTE: Enable CRM Duplicate Detection option is not available when the Action is Delete.
NOTE: In order for CRM Duplicate Detection option to take effect, you need to setup proper duplicate detection rules for the target CRM entity in CRM system.
NOTE: There is a special behavior that you should be aware if you want to use Enable CRM Duplicate Detection option. CRM duplicate detection relies on a CRM Asynchronous service job called Matchcode Update job, which is not a real-time job. For this reason, any records that have been recently (say the last few minutes) added or updated in the system, the CRM system won't have matching code for them until the Matchcode Update job kicks in next time, which usually happens every few minutes, therefore the duplication detection would not take them into account. Due to the mentioned reason, we don't usually recommend relying on CRM duplicate detection on large data load process. A better option would be using Upsert action, and selecting a combination of manually-selected matching fields, which will perform a real-time duplicate check during the data load.
- Upsert Matching Criteria
-
Upsert Matching Criteria option allows you to specify how Upsert action determines whether a record always exists in CRM system. The component support the following three matching option when Upsert action is selected.
- Primary Key - Primary Key option matches CRM records based on their GUID IDs of their primary keys.
- CRM Duplicate Detection - CRM Duplicate Detection option matches CRM records based on the CRM duplicate detection rules that have been setup in CRM.
- Manually Specify - Manually Specify option allows you to choose a combination of CRM fields to be used for the Upsert matching criteria, in order to determine whether a matching record exists in CRM system. When Manually Specify option is selected, you will see checkboxes next to the CRM fields in the grid on Columns page.
Columns page
The Columns page of CRM Destination Component allows you to map the columns from upstream components to CRM fields for the destination entity.
In Columns page, you would see a grid that shows all write-able CRM fields in the destination entity, you may select an input column from upstream component for each CRM field.
When Upsert action is selected in the General page, and the Upsert Matching Criteria option has been chosen as "Manually Specify", you will see a checkbox for each field listed in the grid. You may select one or more fields so their value(s) are used as matching criteria.
NOTE: To maximize the component's performance, it is not advised to select too many fields for the matching purpose. Also, you can add indexes to the matching fields in CRM database to improve the performance for the matching query.
Error Handling page
The Error Handling page allows you to specify how errors should be handled when they happen.
There are three options available.
- Fail on error
- Redirect rows to error output
- Ignore error
NOTE: Use extra caution when selecting Ignore error option, since the component will remain silent for any errors that have occurred.
License Manager
SSIS Integration Toolkit comes with a license manager program that helps you manage and activate the product license key to be used for the toolkit.
If you just want to evaluate the software functionality within Business Intelligence Development Studio (Visual Studio), you don't have to acquire a software license.
If you want to test out the functionality by scheduling your SSIS packages, a trial license can be requested. To do so, you can launch License Manager program, then click "Change License Key" button, where you can request a free trial license after filling Licensee Information.
If you have purchased a software license from us, you can also click "Change License Key", so that you can enter the product license key that you have received from us in order to activate the software.
To request a free trial license or activate a product license key that you have received, you can use Web Service option to complete the process by sending the request to our license server directly. An Internet connection is required when Web Service option is used, which is the option that we recommend.
Alternatively, you can choose Email option so that the license manager will generate an email for you which you can send to us. Email option should only be used if your system has no access to Internet, and it is going to be a manual process, so you would expect to wait for 24 to 48 hours before receiving a license file from us. Once you have received the license file from us from your email account, you can save it to a local file, which you can then install by clicking "Install License File..." button in License Manager.
NOTE: In order to activate a license through Web Service, or install a license file, you need to login to the system with a user account that has proper privileges in the system. The license file is stored in Windows registry so that all components of the toolkit can access.
Contact Us
If you need any assistance with the toolkit, please don't hesitate to contact us.