Data transfer into CRM databases with SSIS

Last Friday I received a task of loading some information from a data source (which can be a SQL table, a flat file, an Excel sheet...) into a CRM database. It cannot be done directly most of the times so a web service is need to be used. Since Microsoft CRM data is stored in SQL Server the most straightforward way to do that is using SSIS.

With the help of this great blog post (from Darren Liu - many thanks for him!) with examples and very detailed instructions I was able to do that. With solid SSIS background the most important part of that post was the SSIS script component because I'm quite new to C#. As Darren outlined you have to add the CRM service web reference to the script component and after that the structure itself is quite clear and simple. With the help of the sample code the namespaces and objects can easily be identified and based on those you can customize the solution according to your own needs.

I managed to create my own SSIS package that loads 2 rows to different destination tables (1 to contacts 1 to incidents) but it's quite slow (though the reason could be my VM but other tasks are running a lot faster on that machine, too). If you check the progress pane during/after the execution it tells you every time that [SSIS.Pipeline] Information: "component "Script Component" (28)" wrote 0 rows. I think it might be due to the fact that this script component is a transformation (not source or destination) and by default it just alters rows not actually load them into a database. (You don't even have an output in the current case!) However since it's the same task type it's a kinda strange behaviour but that should be the biggest problem with it :)

Published Sunday, June 20, 2010 11:20 PM by MartinIsti
Filed under: , ,


No Comments
Powered by Community Server (Commercial Edition), by Telligent Systems