Jun
16
2010

SSIS with Dynamics CRM: Case study from a large bank

Two years ago, I wrote a short blog post here about using SQL Server Integration Services (SSIS) with Microsoft Dynamics CRM.

There are 2 ways that you can work with CRM programmatically:

  • The supported way, through the Web services API that the application exposes
  • The, uh, less supported way, by interacting directly with the database where CRM stores its data

And we've got some information for you today on both approaches!

  1. First, our feature article, "Uses for SSIS with MSCRM In a Banking Environment," by Or Hiltch, discusses the less supported approach of interacting directly with the database where CRM stores its data.
  2. At the bottom of this blog post, I have some information about new custom components that use the supported Web services API to work with CRM.

About our feature article on SSIS with CRM

Developer Or Hiltch from Israel ("Or" means "light" in Hebrew) has this to say about himself:

"Nowadays I'm lead developer in a bank project, so I use SSIS to write dtsx packages to create and modified the CRM Database according to business rules - stuff like credit scoring, recalculation of mortgage interests, etc. Actually I'm using SSIS for CRM for about four years now, I'm pretty sure we are one of the first companies to use it in large production MSCM deployments. We had allot of homework to do to study the DB structure, using SQL Profiler to find out exactly what the supported API does so that we could imitate it when using SSIS to directly insert and update the DB."

I found Or on the Web and learned about his interests in an unusual way. While searching for people talking about SSIS, I found Or's Flickr page, with a screen shot of the SSIS package he was working on (How many people upload pictures of their SSIS package to Flickr?), and this caption: "Nowadays at work I am using Microsoft SSIS ETL capabilities to interface banking systems data with Microsoft Dynamics CRM databases. It's lots of fun compared to "pure" T-SQL or and OLE-DB C# program, although I find myself missing the coding bit."

So I found a way to contact Or, and he graciously provided this description of his project. I have added in brackets [] a few points that I asked the author to clarify.

Uses for SSIS with MSCRM In a Banking Environment, by Or Hiltch

In most banking customer relation management systems there arises the question of how to approach the processing of large amounts of data.

In Microsoft CRM 3.0 implementation we are using we have chosen SSIS for performing tasks such as this, and for good reasons.

Firstly, while there is a "supported" way to generate database records in Microsoft CRM using the web service API provided with the product, the generating of tens of thousands of records using this method takes ages. Each web service call triggers an entire chain of events ("callouts"). But even without those, creating a large amount of records iteratively is not a feasible solution for loading massive amounts of data to SQL Server. If performance is important, bulk loading is the way to go.

Secondly, SSIS allows us reuse of .NET code – we are able to use DLLs which encapsulate business logic that we use in the native ASP.NETmechanism of MSCRM ("callouts") with SSIS using the script component.

Two examples of these are the marketing module and the mortgages module.

The Marketing Module Example (CRM with SSIS using the SSIS API)

A part of the bank's marketing strategy which relies heavily on MSCRM is the use of campaign activities: whenever the bank goes public about a new product (say, a nice mortgage offer), marketing analysts produce flat files which contain information about customer who might be interested at that product. That customer list needs to go through a certain transformation, the result of which is a list of "phonecall activities" ordered in "working queues".

Using SQL Profiler, we tracked down the important "under the hood" SQL logic that is performed by the provided web service API. This enabled us to write SSIS packages to perform transformation of flat file data to complicated entity schemas directly against the SQL DB in a bulk fashion and without triggering post-events.

 

Figure 1 (click for full-size image): a part of the package that deals with the inserting of data directly into MSCRM tables. SQL Server Profiler reveals that data has to be inserted into more tables than might be expected

Next, using the SSIS API to execute and monitor dtsx packages, we have written a windows service comprising of a file system watcher over a certain folder in the server.

Finally we edited the MSCRM UI to support upload of files onto that folder, so that the marketing analysts would be able to trigger the file system watcher which invokes the SSIS package.

[Doug: Q. What do you mean by "edited the MSCRM UI?" A. MSCRM has a build-in customization ability.You can refer the application to applications of your own. In this case we added a button using the isv.config - this is a file provided by Microsoft to allow ISV's to add buttons and other UI elements to the MSCRM forms. We defined a JavaScript event (onclick) over that button to open a custom ASPX page (we wrote that completely) that acts as a file uploader.]

 

Figure 2 (click for full-size image): File upload dialogue integrated in MSCRM. Used to allow certain users to upload flat files to the server in order to trigger a file system watcher which executes the SSIS package using the SSIS API.

The Mortgages Module Example (Reuse of .NET Code with CRM and SSIS)

A major part of the bank's CRM system is the mortgages module. This module is basically the banks method of approving a mortgage loan to customers, making the Dynamics CRM implementation somewhat of an "xRM".

[Doug: Q. What is "XRM?" A. Using the Microsoft Dynamics CRM platform to build business applications that have nothing to do with the "C" in "CRM" - for instance, as a platform for a banking and mortgage services. There's a cool video presentation of that made by Microsoft guys here: http://www.xrm.com/]

As a part of the mortgage approval procedure, a customer is given the opportunity to choose several different tracks from which his loan is composed. Each track can have different specifications – for instance, a mortgage loan can be composed of four different loans, each of which with different interest rates, each can spans over different time periods.

Every loan track has a base interest called "anchor interest" which is subject to changes due to regulation or bank policy. To support this "online", when the banker sits with the customer in front of the MSCRM mortgage form (the ASP.NET portion of the application), he uses certain web forms which have DLLs with classes that perform interest calculation according to financial formulas.

[Doug: Q. Are the Web forms and the DLLs custom pages and DLLs that you (or your company) coded? A. Regarding the web forms and custom dlls - we have coded all of them indeed. Some are class libraries, some are web service application, some asp.net web forms. There are allot of ways to attach yourself to the Dynamics CRM platform.]

On the other hand, there is a need for a batch process that runs "offline" on a certain basis – whenever an interest rate changes, the amount of money the customer has to pay in that certain track need to be recalculated.

Again, we are talking about massive amounts of data (tens of thousands of records that represent parts of a mortgage loan) which has to be processed every couple of days.

Like in the marketing example, using SSIS speeds up the transformation and DB access process. But this time, we also enjoy the SSIS Script Component ability to use .NET assemblies over each record in the pipeline. So basically in the writing of the SSIS package the business logic part is already written, and all that's left is the reading and writing of the data from the database.

[Doug: Q. When you say “the business logic part is already written,” do you mean that you are re-using the same custom assemblies that you also use at other locations in the system? A. Exactly. Mortgage calculator is a custom assembly. The reason there is no using statement in the screenshot (way to go for noticing that by the way) is because I'm currently authoring the DLL to change something and it's more comfortable to do that with the code written in the same assembly (nowadays this DLL is mainly used with that SSIS).]

 

Figure 3 (click for full-size image): enjoying reuse of .NET code – the mortgage calculator class - when using SSIS to perform transformation on large amounts of data in an MSCRM mortgage module.

New SSIS custom components for Dynamics CRM

CozyRoc has just released the beta of version 1.5 of its library of custom components called SSIS+. This new version contains the following new components, which use the supported API to interact with CRM:

Ivan Peev of Cozyroc provided me with some additional information about these new components:

"The CRM components are using only the published web interfaces for interacting with the system. Some people may say this will make them slower compared to direct insert into a table , but we decided integrity and compatibility are more important to customers than performance. Also the solution supports all three types of CRM deployment: Premise, Live, Hosted."

Comments on my blog are disabled, but you can still email me

Deleting the flood of spam comments simply got to be too much. But please don't hesitate to contact me at dougbert at dougbert.com.

Thank you for following!