Integrating CRM using SQL Integration Services (SSIS)

re: Integrating CRM using SQL Integration Services (SSIS)

caims — Thu, 08 May 2008 03:39:48 GMT

It is a great way to import data into mscrm!

re: Integrating CRM using SQL Integration Services (SSIS)

jason — Mon, 12 May 2008 23:00:06 GMT

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.

jason

re: Integrating CRM using SQL Integration Services (SSIS)

Finnur Eiríksson, Reykjavik Iceland. — Wed, 14 May 2008 14:12:04 GMT

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!

re: Integrating CRM using SQL Integration Services (SSIS)

Or Hiltch — Thu, 15 May 2008 22:09:09 GMT

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.

re: Integrating CRM using SQL Integration Services (SSIS)

Marcos — Fri, 23 May 2008 18:46:29 GMT

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.

re: Integrating CRM using SQL Integration Services (SSIS)

Marcos — Wed, 28 May 2008 18:30:18 GMT

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)"

re: Integrating CRM using SQL Integration Services (SSIS)

Alex — Tue, 03 Jun 2008 00:48:58 GMT

Hello together,

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

Task script:

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Imports Crm.Proxy.CrmSdk

Imports System.Xml

Imports System.Web.Services

Public Class ScriptMain

Inherits UserComponent

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

service.Execute(create)

End Sub

End Class

Could anybody help?

Thanks in advance.

Alex

re: Integrating CRM using SQL Integration Services (SSIS)

Alex — Tue, 03 Jun 2008 11:19:06 GMT

Is there any solution for the problem above?

Thanks in advance.

re: Integrating CRM using SQL Integration Services (SSIS)

vladimir.ljubibratic — Sat, 14 Jun 2008 01:04:56 GMT

Hello,

Same issue as described above - Server was unable to process request. Any suggestions?

Thanks in advance.

Vladimir

re: Integrating CRM using SQL Integration Services (SSIS)

Keelio Software — Mon, 16 Jun 2008 06:01:17 GMT

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

re: Integrating CRM using SQL Integration Services (SSIS)

Keelio Software — Mon, 16 Jun 2008 06:02:29 GMT

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

re: Integrating CRM using SQL Integration Services (SSIS)

SSIS4CRM — Thu, 24 Jul 2008 17:29:50 GMT

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.

re: Integrating CRM using SQL Integration Services (SSIS)

setras — Tue, 29 Jul 2008 07:56:18 GMT

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.

re: Integrating CRM using SQL Integration Services (SSIS)

SSIS4CRM — Wed, 06 Aug 2008 11:08:01 GMT

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

re: Integrating CRM using SQL Integration Services (SSIS)

Pam@Teccora — Tue, 16 Sep 2008 20:04:44 GMT

This worked nicely on my dev box. However in production i am getting an authentication error. any ideas?

re: Integrating CRM using SQL Integration Services (SSIS)

sjh — Thu, 06 Nov 2008 00:31:32 GMT

We are currently running CRM 4.0 and SQL Server 2005. I have a request to load customer account and product information from our ERP system to the CRM. I noticed a comment above to never create entries but doing updates even though it is not supported will work fine.

Can I create entries and do updates and deletes or is there a preferred method for doing this?

Is SSIS the best way to accomplish the task?

re: Integrating CRM using SQL Integration Services (SSIS)

Payungsak Aramvuttanagul — Thu, 06 Nov 2008 13:14:43 GMT

This is easy way for import to CRM entity.

prerequesit : SSIS + .NET

re: Integrating CRM using SQL Integration Services (SSIS)

KS — Tue, 13 Jan 2009 17:42:44 GMT

I am used the above method successfully on my development environment, but I am now trying to release on to the client server.

On the client server the package can not find the proxy.dll file.

Any ideas where I should place this, or do you have to create the dll and the package locally?

re: Integrating CRM using SQL Integration Services (SSIS)

Jeff W — Mon, 19 Jan 2009 22:31:45 GMT

This article seems great for pushing data to CRM, however is it possible to do something similar for pulling information from CRM? I've looked and there seems to be very little online about getting CRM data out an into other systems using the web services.

Maybe I'm missing something.

Thanks,

Jeff

re: Integrating CRM using SQL Integration Services (SSIS)

Peter — Thu, 29 Jan 2009 12:02:38 GMT

Great article

I would also like info on exporting data from CRM eg when a new account ois created would like to export account number, name, etc to an external database to then upload into GP or other. Any suggestions ?

re: Integrating CRM using SQL Integration Services (SSIS)

Shanmugam — Wed, 04 Feb 2009 07:43:36 GMT

Hello everybody,

I'm Shanmugam, new in this blog and also CRM, when i tried this solution, but I get the error described below. I don't know what's wrong.

Can you help me please? Thanks

Script Component: Runtime Error:-

--------------------------------

Script Component has encountered an exception in user code:

Project name: SC_d7c1f95c7a6a4049a6cfeec1b58e20a0

The request failed with HTTP status 401: Unauthorized.

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 ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)

at UserComponent.Input0_ProcessInput(Input0Buffer Buffer)

at UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

So Plz anybody Help me.....

Thank in advance

re: Integrating CRM using SQL Integration Services (SSIS)

shanmugammsc — Thu, 05 Feb 2009 10:30:08 GMT

Its Very Urgent Plz Help me quick,

System Requirement :-

MS CRM 4.0

SQL Server 2008 SSIS

that above article is not working for this specification. How to Use this plz tell me ...

re: Integrating CRM using SQL Integration Services (SSIS)

jack jones — Thu, 12 Feb 2009 18:53:09 GMT

Darren, I get to about 4000 - 5000 records inserted, in the contact entity, using your example, and then I GET this error every time

in my SSIS package: (I have 50,000 records to insert!)

Please Help!

- Jack

[Script Component [945]] Error: System.Net.WebException: The request failed with HTTP status 400: Bad Request. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket)

re: Integrating CRM using SQL Integration Services (SSIS)

Halldor Johannson — Fri, 13 Mar 2009 18:06:00 GMT

Jack Jones,

If it always fails on the same record you should double check the data for that record if it is ok.

If it is random between 4000-5000 the problem might be related to the connection being cutoff after a set amount of time this started happening in crm 4, if you are using the CRMWebService inserting then check your connection class and add the following line "YOURCRMWebServiceNAME.UnsafeAuthenticatedConnectionSharing = true;" make sure that once you are finished importing to put this value to false as this can pose a security risk.

And ofcourse use at your own risk 8)

re: Integrating CRM using SQL Integration Services (SSIS)

Darren Liu — Wed, 25 Mar 2009 22:08:54 GMT

Hi,

You might want to reduce your SSIS thread to 2 instead of 5. Also please check out a blog post by my friend Luke on increasing the sockets and decrease the tcp time waited delay on the server. It should help you resolving the web service issue.

http://www.crowehorwath.com/cs/blogs/crm/archive/2009/01/15/crm-webservice-error-only-one-usage-of-each-socket-address-protocol-network-address-port-is-normally-permitted.aspx

Regarding to doing this in SQL 2008, you might not need the Proxy class since you can call web service in a component.

re: Integrating CRM using SQL Integration Services (SSIS)

Darren Liu — Wed, 25 Mar 2009 22:09:41 GMT

Forgot to post the URL for the blog.

http://www.crowehorwath.com/cs/blogs/crm/archive/2009/01/15/crm-webservice-error-only-one-usage-of-each-socket-address-protocol-network-address-port-is-normally-permitted.aspx

re: Integrating CRM using SQL Integration Services (SSIS)

Maxim — Fri, 27 Mar 2009 09:24:40 GMT

Great post. Thanks a lot.

By the way can somebody tell me - can I use such approach to export data (for example accounts or contacts) from CRM 4.0?

re: Integrating CRM using SQL Integration Services (SSIS)

BHogan — Wed, 22 Apr 2009 16:17:06 GMT

Does anyone know if this approach works with CRM Online? A webservice is a webservice ... right?

re: Integrating CRM using SQL Integration Services (SSIS)

Mona Salama — Wed, 20 May 2009 11:37:30 GMT

Great article but is there is away to export from MS CRM into my Database ?

re: Integrating CRM using SQL Integration Services (SSIS)

kjo — Tue, 23 Jun 2009 19:11:49 GMT

Is it possible to map sql data according to the ownership of the records and then import them into ms crm. Or is is possible to map multiple data tables to a single entity using SSIS??

re: Integrating CRM using SQL Integration Services (SSIS)

Finnur Eiríksson — Fri, 03 Jul 2009 15:18:20 GMT

I have noticed that some of you are having trouble getting data out of CRM. I'm writing this with those of you in mind that have the task of keeping another system in synch with CRM, (i.e. CRM then being the Source and some other system being the Target).

For those of you that are in the position that CRM is not the main source of data (like in a company where data changes are performed in two systems, in my case ADAM and CRM), the first option discussed below is in my opinion the right choice despite the drawbacks.

Those of you that are in the sticky situation where data is changed in multiple systems (more than two) then I would advise you to turn your sights to the concept of Master Data Management, which is a totally different approach.

I've tried a number of approaches when identifying the data set that has changed in my customers CRM system (i.e. the delta), mostly using SSIS and a CRM proxy class as proposed by Darren.

The main issue here is that CRM is not very good at pushing data (or rather entries) that has changed.

I propose three alternatives:

(If anyone has other ideas, please share this with the resst of us ;)

1. (push approach)

Have a CRM programmer create workflows that pushes changes out of CRM and perform updates in the Target system through the target systems webservice.

This solution (having CRM push data directly into the Target) a suboptimal approach with regard to system integration best practices since this type of integration pattern is known as point to point, which is OK if you're doing a simple integration soultion, but it will clutter up pretty quickly and you'll end up with a spaggetti integration scenario.

If your Target does not have a webservice, then what you could do is to have the workflow write CRM entries to a change-table and then use SSIS from there on to the Target via OleDb or ODBC.

This can also be done by creating a plug-in, instead of a workflow, that runs when data is changed in CRM.

Remember that if CRM pushes changed data into a table into your change table, for SSIS to chrunch on and do the final step of porting data into the Target, it would be wise to only push the entity ID (AccountId, ContactId etc), and then have SSIS select from the change table and doing a distinct select (Like: SELECT distinct(AccountId) FROM dbo.YOURCHANGETABLE), so that multiple changes on the same Contact or Account only result in one single update on the Target system. SSIS then fetches fresh CRM data (including all relevant columns, even those that have not changed, otherwise you would have to implement delta analysis on column level) based on the ID in the change table and updates the Target.

2. (push approach)

Another approach that came into play with SQL Server 2008 is CDC/CDT, Change Data Capture/Change Data Tracking. (For info please visit: http://msdn.microsoft.com/en-us/library/bb933994.aspx)

This option is a good choice if you do not have extensive knowledge of the inner workings of CRM.

CDC would then push data changes into tracking tables which SSIS reads from.

Use CDC if you want to track changes on column level (if you want to know exactly which column has changed, the value before change and value after change, this could be useful for audit-trail or logging purposes). Use CDT if it's enough for you just to know which entries that have been changed in CRM.

3. (pull + delta analysis)

The third approach would be to have a SSIS package run periodically, and always keep a complete set of CRM data in intermediary tables, and the use the data set from last run and do a complete delta analysis with current CRM data using conditional splits or in code in a script component, in order to find out

which entries have changed in CRM.

This approach works fine in theory but its not really straight forward to implement this in SSIS. I ended up with a huge SSIS package with suboptimal performance once running in the production environment.

Best regards and good luck!

re: Integrating CRM using SQL Integration Services (SSIS)

David Withers — Tue, 07 Jul 2009 01:25:50 GMT

Does anyone have the code converted to C#?

re: Integrating CRM using SQL Integration Services (SSIS)

Finnur Eiríksson — Thu, 09 Jul 2009 17:01:30 GMT

Try any code converter, like: http://www.developerfusion.com/tools/convert/vb-to-csharp/

re: Integrating CRM using SQL Integration Services (SSIS)

Finnur Eiríksson — Thu, 09 Jul 2009 17:20:25 GMT

A little more on Change tracking.

This article discusses change tracking in SQL 2005. This might do the trick of pushing changes out of CRM (or rather SQL Server).

I haven't tested it, but it seems to be promising ;)

http://www.sqlservercentral.com/scripts/change/67380/

re: Integrating CRM using SQL Integration Services (SSIS)

DV — Tue, 11 Aug 2009 15:51:51 GMT

I tried the same apporach in SSIS script task to import the data into CRM. But in my 64 bit system i am getting the following error:

Unable to generate a temporary class (result=1).

error CS0001: Internal compiler error (0xc00000fd)

error CS0003: Out of memory

System.Exception {System.InvalidOperationException}

Please suggest.

re: Integrating CRM using SQL Integration Services (SSIS)

Anne — Wed, 12 Aug 2009 01:18:14 GMT

Nice post! This helped me get started with my integration project (newbie!).

I run the above and get a success on finish, but 'wrote 0 rows'. I put a debug on the Data Flow Path between the OLE DB Source and the Script component, and see all the records that are getting pushed/passed to the Script component. But yet 0 rows are written. I can set this to push 1 row of data and it works fine.

Any ideas?

Thanks

re: Integrating CRM using SQL Integration Services (SSIS)

Jena — Thu, 20 Aug 2009 12:33:59 GMT

If I run the SSIS package through a SQL JOB, I am getting the following error(FYI... running Package from developme is working fine):

System.IO.FileNotFoundException: Could not load file or assembly 'CRM.Proxy, Version=1.0.0.0, Culture=neutral, PublicKeyToken=1161469184bd9e00' or one of its dependencies. The system cannot find the file specified.

Please help.