Today we welcome our guest blogger CRM MVP Darren Liu from the Crowe company.
Many Microsoft Dynamics CRM (MSCRM) implementations involved integration with other systems. When I think of integrations between systems, the common approaches pop up immediately in my mind are BizTalk, Scribe or to write a custom integration framework using the CRM Software Development Kit (SDK).
By working with Darren Hubert, Solution Architect from the Microsoft National Service Pursuit Team in my last project, I have discovered another approach that I encourage you to consider in the your integration project which involves using SQL Integration Services (SSIS) to integrate with Microsoft Dynamics CRM.
SSIS is a platform for building data integration solutions and it is a service that runs on the SQL Server. SSIS replaces the existing Data Transformation Services (DTS), which was introduced to the market as a component of SQL Server 7.0, and runs a unique package which stores the design of an ETL (Extraction -> Transformation -> Load) process.
Because SSIS in SQL 2005 was difficult to connect to Web Services by default, I did not spend much time into my research for my previous integration projects. Thanks again to Darren Hubert and his friends, who showed me a work around using a CRM proxy class in my last project. I was able to successfully integrate CRM 4.0 using SSIS. Here I would like to share with all of you on what I have learned so that you can leverage it on your next CRM integration projects.
Before we get started, here’s the list of requirements:
In this blog, I will use a simple example to show you how to send contact data stored in a SQL database to MSCRM 4.0 via CRM Web Services using SSIS.
The source data is from the data from the other system that you would like to send to the CRM system. You source data can be a text file, an Access database, an Oracle database, etc… In this example, I will create a simple Contacts table in a SQL database that’s already existed in environment.
Source Table: Contacts
1: CREATE TABLE [dbo].[Contacts](
2: [ContactId] [int] IDENTITY(1,1) NOT NULL,
3: [FirstName] [varchar](50) NULL,
4: [LastName] [varchar](50) NULL,
5: [Phone] [varchar](50) NULL,
6: [Email] [varchar](50) NULL,
7: CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED
9: [ContactId] ASC
10: )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
11: ) ON [PRIMARY]
14: INSERT INTO Contacts (FirstName, LastName, Phone, Email)
15: VALUES ('John','Smith','312-888-8888','email@example.com')
17: INSERT INTO Contacts (FirstName, LastName, Phone, Email)
18: VALUES ('Darren','Liu','312-999-9999','firstname.lastname@example.org')
20: INSERT INTO Contacts (FirstName, LastName, Phone, Email)
21: VALUES ('Adam','Johnson','312-555-5555','email@example.com')
Create CRM Proxy Class
The SSIS framework provides a Web Service Task which executes a Web Service method, however it’s difficult to use. To reduce the complexity of the SSIS integration with CRM, generate a CRM Proxy class using Visual Studio. This will make the integration process much smoother and you will encounter less road blocks.
Start a New C# Class Library Project
Sign the Project
Add CRM Web Services
Add CRM Web Service and CRM Discovery Service to the CRM.Proxy project. Visual Studio will automatically run WSDL.exe in the background to create a proxy class for these two CRM Web Services which we will use later on in building the SSIS package.
Deploy CRM Proxy Class to SSIS
In order to use the Crm.Proxy library in our SSIS package, we need to GAC the Crm.Proxy.dll and also copy the Crm.Proxy.dll to the .Net Framework 2.0 folder. The default location of the .Net Framework folder is C:\Windows\Microsoft.Net\Framework\v2.0.50727.
To GAC the Crm.Proxy.dll, you can simply drag and drop the dll file to C:\Windows\assembly folder.
Create SSIS Package
Start a New Integration Service Project
After creating the project, follow the steps below to setup the SSIS package.
Add Control Flow Items
Drag and drop “Data Flow Task” from the Toolbox to the Control Flow Design Pane.
Add Data Flow Items
Double click on the Data Flow Task item and it will take you the Data Flow Design Pane. In here we will specify the source data and also write script to send data to CRM.
Specify Source Data
Setup Script Component
Add CRM Proxy Class to SSIS
Since we generated and GAC the Crm.Proxy library in the step above, we will add a reference to the proxy class in this step.
Coding the Package
In order to complete the script, we also need to add the following reference: System.Web.dll, System.Web.Services.dll and System.Xml.dll. Then add the following imports statement
Imports Crm.Proxy.CrmSdkImports System.XmlImports System.Web.Services
Imports Crm.Proxy.CrmSdkImports System.XmlImports System.Web.Services
Lastly, copy and paste the following code to the ScriptMain section:
1: Public Class ScriptMain
2: Inherits UserComponent
3: Dim Service As CrmService
5: Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
7: ‘Create Contact object
8: Dim contact As New contact()
10: contact.firstname = Row.FirstName
11: contact.lastname = Row.LastName
12: contact.telephone1 = Row.Phone
13: contact.emailaddress1 = Row.Email
16: End Sub
18: Public Overrides Sub PreExecute()
21: ‘Create CRM Service
22: Service = New CrmService()
23: Service.Credentials = System.Net.CredentialCache.DefaultCredentials
24: Service.Url = "http://localhost:5555/MSCrmServices/2007/CrmService.asmx"
25: Dim token As New CrmAuthenticationToken()
27: ‘In this example, my organization name is MicrosoftCRM
28: token.OrganizationName = "MicrosoftCRM"
29: Service.CrmAuthenticationTokenValue = token
30: Service.PreAuthenticate = True
31: End Sub
33: End Class
Execute the SSIS Package
After done coding the SSIS package, right click on the Contact.dtsx package and then select Execute Package. After the package executed successfully, we should see the records in MSCRM.
Deploy SSIS Package
After successfully test the package, deploying the package is pretty easy. Just follow the steps below.
After the package is deployed to your SQL server, setup a SQL agent job to execute the package according to your integration time interval.
That’s it! Hopefully you have gotten the idea of how to leverage the power of SSIS to send data to CRM. In the example here, I only demonstrated how to create records in CRM. In the actual integration implementation, you will also need to consider how to update/delete/link records, and also error handling. I hope this will help you in your next CRM integration project.
It is a great way to import data into mscrm!
If i had to make 100's of batch updates to CRM, would I be better of using the CRM object model to update the db or use the web service? Can the CRM web services handle 100's of updates simultaneously?
Thanks for your help and this good article.
I have been using a similar approach.
However if your project aims at doing:
1. a create for new entries in your source and
2. an update for all existing entries using fresh data from the source...
a problem occurs when you have 10K entries or more since doing a direct update on the CRM database clearly outperforms doing the same update through the webservice interface.
Please observe that you should NEVER EVER create entries in the CRM database, its a BIG NoNo, do that and you will burn in hell. In the case of doing updates that is not recommended by the Microsoft team, however I've been doing it for 2 years now in a number of CRM implementations without any problems what so ever.
My approach uses 2 dataflows, one for creating an instance of the entity that you are working with, and a second dataflow that only does updates.
If there is a unique identifier present in your source data you could use this approach.
Let me explain...
Lets say that you are working with the Account entity and have something like social security number. Do an inner join between the source data and the Account table in the CRM database using a Merge Join shape and fetch the AccountId. If the join does not yield an AccountId (AccountId = NULL) would mean that a new entry has been added to your data source that is not present in the Accounts table. Use this information in a Conditional split, define an output that only contains source entries that are not present in CRM and then do a create using Darrens solution as described above.
In the second dataflow read your source data do the same inner join as in the first dataflow and then do an update on the CRM tables involved using a OleDb destination shape. NOTE: when you do the update remember to include a WHERE statement using the AccountId fetched from CRM.
Schedule your package in SSIS and create a job in the SQL Agent, then specify a schedule for the job so that it runs when you want it to.
This is a feasible approach if you want a fast solution that runs on a dialy/hourly basis doing both creates and updates the data in CRM.
As this is my first blog entry EVER, I hope that someone can make use of it. I relise that it's a bit cluttered. Good luck everone!
Nice idea. This approach of using SDK via SSIS would be useful mainly becaus of the benefits SSIS gives you for parsing the input.
Still, SDK creations are not a feasible solution by any means for scheduled integration tasks in large scale (I learned it the hard way after I had to rewrite projects because of the slow performance of the SDK). For that you should replace the sdk part with execute script tasks that insert/update from middle tables. It might be unsupported but it seems to be the best option.
Very nive article!!! I found it very interesting because I'm learning SSIS to integrate and keep synchronized my developments in GP with some entities in CRM.
I've tried this solution, but I get the error described below. I don't know what's wrong.
Can you help me please? Thanks
"Server was unable to process request.
at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object parameters)
at Crm.Proxy.CrmSdk.CrmService.Create(BusinessEntity entity)
at ScriptComponent_27fde95cbc6a4efcb88cd55a12cc0d04.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)
at ScriptComponent_27fde95cbc6a4efcb88cd55a12cc0d04.UserComponent.Input0_ProcessInput(Input0Buffer Buffer)
at ScriptComponent_27fde95cbc6a4efcb88cd55a12cc0d04.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)"
thanks giving me idea how to get the migration done, but i get a similar failure:
bei System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
bei System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object parameters)
bei CRM.Proxy.CrmSdk.CrmService.Execute(Request Request)
bei ScriptComponent_2960c719f4304f45bebd2500037e9e67.ScriptMain.Eingabe0_ProcessInputRow(Eingabe0Buffer Row) in dts://Scripts/ScriptComponent_2960c719f4304f45bebd2500037e9e67/ScriptMain:Zeile 36.
bei ScriptComponent_2960c719f4304f45bebd2500037e9e67.UserComponent.Eingabe0_ProcessInput(Eingabe0Buffer Buffer) in dts://Scripts/ScriptComponent_2960c719f4304f45bebd2500037e9e67/ComponentWrapper:Zeile 29.
bei ScriptComponent_2960c719f4304f45bebd2500037e9e67.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer) in dts://Scripts/ScriptComponent_2960c719f4304f45bebd2500037e9e67/ComponentWrapper:Zeile 21.
bei Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)
System: CRM 3.0
Public Class ScriptMain
Dim Service As New CrmService()
Public Overrides Sub Eingabe0_ProcessInputRow(ByVal Row As Eingabe0Buffer)
Service.Credentials = System.Net.CredentialCache.DefaultCredentials
Service.Url = "http://localhost:5555/MSCrmServices/2006/CrmService.asmx"
Dim product As New product()
product.name = Row.ARTBEZ
product.productnumber = Row.MASKENKEY
Dim target As New TargetCreateProduct()
target.Product = product
Dim create As New CreateRequest()
create.Target = target
Could anybody help?
Thanks in advance.
Is there any solution for the problem above?
Same issue as described above - Server was unable to process request. Any suggestions?
If your looking at speeding up your integrations with SSIS and Dynamics GP with CRM you should take a peak at the Dynamics GP SSIS Toolkit at http://www.keelio.com
The SSIS4CRM Proxy Generator helps you do all off this procedure from a nice tool.
All you need to do is install the SSIS4CRM Proxy Generator , load it, provide the URL for your MscrnService.asmx or MetadtatService.asmx and the tool will read al the schema from this location, create a class library, compile it, give it a strange name, save it to the .Net folder on your computer and write it into the GAC.
Now all you have to do is reference the DLL from the SSIS script component and use it.
Great article Darren, thank you.
Marcos, Alex, and Vladimir:
I also was encountering the Pipeline Buffer error noted above. My CRM deployment was configured over port 5555.
To resolve the issue I added the entry MaxUserPort to the CRM web Server registry as detailed in the following Microsoft Support Article http://support.microsoft.com/kb/Q196271
Hope this helps.
I made a tool that helps generates the DLL required to use from SSIS script component to write Microsoft SDK code. You can download this too for free from this location http://www.ssis4crm.com
This worked nicely on my dev box. However in production i am getting an authentication error. any ideas?