- 1 Introduction
- 2 Requirements
- 3 Video Tutorial – CRM Insert, Upsert, Delete, Update
- 4 Getting Started
- 4.1 Configure SSIS Dynamics CRM Connection
- 4.2 Bulk Insert – SQL Server data Import into Dynamics CRM using SSIS
- 4.3 Bulk Upsert – Update or Insert Dynamics CRM Records in SSIS
- 4.4 Bulk Update – Modify Dynamics CRM Records in SSIS
- 4.5 Bulk Delete – Delete Dynamics CRM Records in SSIS
- 5 Error Handling
- 6 Handling Special Data Types (PartyList, Lookup, PickList)
- 7 Conclusion
In our previous article, we talked about how to read from Dynamics CRM. Now let’s look at how to Load data into Dynamics CRM using SSIS. We will see a few important aspects of loading data into Dynamics CRM Online (i.e. Dynamics 365) and On-premises / IFD (Internet facing hosted instance).
We will show you how to use SSIS Dynamics CRM Destination connector for Bulk Insert, Update, Delete and Upsert Operations. This article uses Dynamics 365 Online for CRM Integration Examples but concepts are same for other types of installation (e.g. IFD or On-Premises)
- Make sure you have SSIS Designer installed (i.e. SSDT-BI)
- Download and Installed ZappySys SSIS PowerPack which includes an SSIS Dynamics CRM Connectors.
- Finally, make sure you have Microsoft Dynamics CRM credentials handy.
That’s it. So let’s get started.
Video Tutorial – CRM Insert, Upsert, Delete, Update
In this section, we will see how to perform bulk insert, update delete and upsert operations using SSIS Dynamics CRM Destination
Configure SSIS Dynamics CRM Connection
The first step to read or write Dynamics CRM data is to make sure we have working Dynamics CRM connection in SSIS.
- Create new or open existing SSIS Project in Visual Studio.
- Open SSIS Package and Go to SSIS designer.
- Right click in the connection manager panel and click New connection:
- In the connection type selection, Select the ZS-DYNAMICS-CRM Connection.
- We will use a CRM Online (Office 365) connection, region, a username and a password and select organization from the dropdown as below and then click Test connection:
Bulk Insert – SQL Server data Import into Dynamics CRM using SSIS
Now, let’s see steps to do SQL Server data Import into Dynamics CRM using SSIS. Below steps assume you already have sample data in some SQL Server Table which will be used to create new CRM accounts. You can enter few account names you like to create in the CRM.
- Open SSIS Package and drag Data Flow task from Control Flow SSIS Toolbox
- Go to data flow designer. Drag and drop OLEDB Source
- Configure OLEDB Source to read from some sample data table (e.g. dbo.Accounts). If you want to be lazy and just test quickly you can use ZS CSV Source (Direct Value Mode) instead of OLEDB Source.Sample Data May be like below
- Drag and drop ZS Dynamics CRM Destination from the toolbox.
- Connect Source to your Destination component.
- Double click CRM destination to configure it.
- On the Connection Managers tab, Select Dynamics CRM connection we created in the previous section.
- On the Component Properties Tab Select Table Name.
- On the Mapping Tab map few columns (e.g. name, address1_city, and address1_country)
- Click OK to save CRM destination.
- Right click in the data flow designer, click Execute Task
Bulk Upsert – Update or Insert Dynamics CRM Records in SSIS
In the previous section we saw how to Insert New records. However sometimes you want to Upsert (Insert or Update). So if record already exists Update else Insert. In order to achieve this you have to set two additional attributes. For CRM Upsert you have to perform few changes to previous steps
Create Alternate Key for CRM Upsert
To perform Upsert operation you can map Table’s Primary Key or Alternate Key. Primary Key usually starts with table name and ends with “id” (e.g. for account table its accountid ).
Many times Source system don’t have CRM Table Primary Key. In such case you may use Alternate Key in the mapping (JOIN Key). To use Alternate key for Upsert you have first define Alternate Key in CRM Portal. Check this link for exact steps.
Configure SSIS CRM Destination for Upsert
- Double click CRM destination. On the Component Properties Tab, Change Operation to UpdateOrInsert
- Specify UpsertKeyField to either Table’s Primary Key Column Name or Alternate Key Name.
- Now go to Mappings tab and confirm Key field is mapped.
- Click OK and Run Package to test CRM Upsert Operation.
Handling Inserted / Updated Records Output
Now lets look at how to detect which records got inserted and which records got Update? In Upsert Mode each record can be directed to one of the following three Outputs.
- Inserted Records [Blue Arrow]
- Updated Records [Blue Arrow]
- Records with Error [Red Arrow]
You can select Red or Blue arrow coming out from CRM Destination component. Drag and drop output arrow on any down stream component.
Bulk Update – Modify Dynamics CRM Records in SSIS
If you want to do bulk Update its very same steps as Upsert Operation except you have to set Operation = Update in the properties grid. For Key field you have to set primary key or Alternate Key.
Bulk Delete – Delete Dynamics CRM Records in SSIS
Bulk delete doesn’t require many settings. All you have to change is Operation = Delete on Component Properties Screen. On the Mapping Screen Map only one column (Primary Key). If you are not sure which one is primary key please refer to the previous section (Upsert Operation).
Some times you may get below errors. If you get this error make sure you delete all child records first before deleting parent. For example if you are deleting account then make sure you have to delete leads / contacts for that account or update leads / contacts so they don’t refer to account being deleted.
Error: 0xC0047062 at Dynamics CRM Bulk Delete, Delete Accounts : System.Exception: Exception: The object you tried to delete is associated with another object and cannot be deleted.
Many times you want to ignore bad records and continue processing good records. In such case you can use Dynamics CRM Error Output. Just grad Red arrow from CRM Destination and Drop on target component. Select Redirect Option for Error , Truncation both and Click OK.
Once you set Error Handling your component will continue to run without failing whole data flow. Later you can review errors (if you saving to destination).
Handling Special Data Types (PartyList, Lookup, PickList)
Certain datatypes in CRM requires special handling. Check this article to learn more about how to insert data into columns with PartyList, Lookup or PickList field.
In this article, We have seen how easy it is to integrate SQL Server data with Dynamics CRM. Using drag and drop connector you can perform Bulk insert, update, delete and Upsert operations. Download ZappySys SSIS PowerPack to learn more about other possible scenarios which are not discussed in this article.