We have often had sync requests projects where we need to write data from CRM to flat files. Our option until now had been to either
- Develop a Windows service, that polls at regular intervals to writes the file
- Develop a Windows app and using the windows task scheduler to schedule to run at regular intervals or specified times.
After exploring SSIS platform, it opens up another option in this direction. We can create SSIS packages, and configure it to run at regular intervals using SQL Jobs.
Here we plan to provide a walkthrough to help other start off with CRM integration using SSIS.
Select Integration Service Project from Business Intelligence tab (Note: You get this tab only when you have BIDS installed) and give a proper name to your project and click Ok.
Next to connect to CRM (here we are taking online CRM) we will need to provide connection credentials. For this we will create package level parameters and later pass them as parameters to our Script component. Right click anywhere on Control Flow tab and select variables as shown below:
Here you can find that we have defined all the credentials that are necessary for connecting to CRM.
For demo purpose we will read two fields (Account Name and Telephone) from Account Entity records and write it to flat file. As shown above Go to -> Inputs and Outputs tab -> add two columns to the Output Columns viz. “AccountName” and “AccountTelephone” as String data types.
Next navigate to -> Script tab and pass the connection credential variables that we created to the ReadOnly Variables as follows:
To register dll in GAC:
- Go to Visual Studio command prompt (Please note that this should be the command prompt of the Visual Studio which you are currently using)
- There type -> gacutil /i “path of the dll along with dll name i.e (.dll)”
In this way we need to register following dll’s :
Please note that all the above dlls which are provided in Microsoft CRM SDK package are built in version 4.0. Hence the project file where we need to add these references should be built in .net framework 4.0 or above. So please check the project built version as follows:
You will need to install .Net Framework Version 4.0 and higher or else you will get build errors related to the version conflict for dll’s and the project won’t build successfully.
Also you need to add System.Runtime.Serialization dll and System.ServiceModel dll.
Now to connect to CRM you can create a Class library which will have code for CRM connection and pass the connection parameters to it. Also refer all the respective dlls in header Namespace as follows:
As shown above define the CRM library and IOrganizationService globally so that you can initialize and use them in later events.
Now InPreExecute event Initialize Organization service by passing the connection parameters as follows :
/// This method is called once, before rows begin to be processed in the data flow.
/// You can remove this method if you don’t need to do anything here.
public override void PreExecute()
_service = lib.InitializeOrgService(Variables.orgURL, Variables.orgName, Variables.userName, Variables.password);
Next on CreateNewOutputRows event you can query CRM using the organization service as follows:
public override void CreateNewOutputRows()
Add rows by calling the AddRow method on the member variable named “<Output Name>Buffer”.
For example, call MyOutputBuffer.AddRow() if your output was named “MyOutput”.
using (OrganizationServiceContext svc = new OrganizationServiceContext(_service))
var accountEntities = from acc in svc.CreateQuery(“account”)
var accountList = accountEntities.ToList();
if (accountList != null && accountList.Count > 0)
for (int i = 0; i < accountList.Count(); i++)
if (accountList[i].Contains(“name”) && accountList[i].Contains(“telephone1”))
string accountName = Convert.ToString(accountList[i][“name”]);
string accountTelephone = Convert.ToString(accountList[i][“telephone1”]);
Output0Buffer.AccountName = accountName;
Output0Buffer.AccountTelephone = accountTelephone;
In above code you can find that using LINQ we fetched Account entity records. Then we loop through each records and get the “name” and “telephone1” field and set it to the Output0Buffer variables viz. “AccountName” and “AccountTelephone” as we defined in the output variables in script.
When you are done with all this coding, save and build the solution and close the solution.
In this way using CRM dlls you can query data and import it and divert it to the destination as per your requirement.
Though you can go ahead and develop SSIS packages using CRM DLL, it might make sense to check out the Dynamics CRM SSIS tools available from Kingsway Soft. This was a simple read operation that we were working on, but for complex write operations to CRM, the prepackaged CRM Adapter for SSIS from Kingsway would always be preferred.
Look Beyond “CRM” in your Dynamics CRM with QuickBooks Integration. For more details get in touch with us on firstname.lastname@example.org.