Thursday, February 11, 2016

Loading data into your CRM Application in the year 2016

Lucky for people working involved in data migrations not much has changed. It is still underestimated, often not part of project plan and still a lot of challenges.

We still have standard CRM Import tool and now also the new Data Loader Service, which I did not had the time to test it (of course for Online).

The other tools mostly in short list are Scribe either Online or Insight and SSIS in combination with kingswaysoft to connect to CRM.

'DLS, possible alternative for CRM Online' 
Still the biggest challenge is how to handle a lot of data, so don't think in thousands, but more in millions of records.  This is still difficult, it might be that the earlier mentioned DLS could help you out for CRM Onlime, but I am not sure yet.

 'Unsupported MS SQL Approach' 
The still fasted method at least for on premise or partner hosted would be migration with MS SQL. There is a catch, this is 'unsupported' which means if you break it you have to fixed it yourself. Nevertheless for consultants which very good understanding of database and impact on GUI of CRM it is solid alternative. It will involve more testing to see CRM is still working, but  you could increase the time for go live migration with days or weeks.


Of course 'unsupported' means that in case you use this kind of migration approach and you need to get Microsoft Support involved it would be a challenge. So be aware that you check your data integrity while using the MS SQL. In my personal experience its lot faster and could save a lot of time also very handy for migrating annotations.

To be supported or not to be supported, that's your choice!

Old School (cloudfree) File Integration

It's already 2016, time flies when you been too busy for blogging :-)

Nevertheless, it is still amazing that integration between to systems is still based on old school text files like (in)famous CSV file.

Of course it still works and once configured  right you have solid integration, but I would still prefer xml file exchange or even better use one of the available adapter for your fav integration tool.

In case you have some need for this ol' skool integration you can do this of course with Scribe Software it's possible with either online or onpremise.

Although the Cloud is hot and we have still have freedom of choice ... yes, you can choose between  cloud and on-premise.. cloud, but if you want it all you have to go cloud and you know cloud is the best. Hmmm, still many customer for MS CRM still have on premise or partner hosted environment....

So, in this short example we still go for on-premise e.g. Scribe Insight / CRM on premise :)

A common scenario that you will need to monitor fileshare and when new CSV file arrive, we have to process it toward Dynamics CRM and either delete or archive the processed file.

First step would be to create ol' skool ODBC 32bit and
Create ODBC 32bit!!

Next step is to define data source name, database directory (windows location) etc, the next step would be to hit "advanced" tab and click define to specify every row. This is important for assigning the data type.

ODBC - Define Table

This will be your data source, of course next step is too create your scribe dts file, but I am sure you would be able to select source (odbc), target (CRM) and create your own mapping.

More interesting are the settings for the integration process and I always forget this, so I have to open and dust off the archive...

To be in style also some screenshots from older Insight version ;-)

In the first step of configuring you
the basics like processe name, collaboration, DTS file and also DTS Parameters where most important is '/SD=%FileName'.

In the second step you can define your pre- and post operation.For the first step you could move incoming file to dedicated directory or might even code to retrieve file from ftp location.

In post command you usually place code to archive you file.Depending on which functionality you use either custom bat file or standard scribe archive solution you can add parameter like '%FileName'

In the third step you location where you file will be placed e.g. location where you put your file for processing. You can use wild card like '*.csv' or in screenshot '*.xml'

Further you can define poll time, run it once of everytime new file arrives and locking file, delete after processing. This would depends on your design.

Last two steps
The last two step are basic, so that shouldn't be a problem.

Bat File
One of the other challenge is to get the right file name in your bat file. The trick is to config the parameter in step one. In your bat file you can refer to it with '%1'. A codesnippit including creation of log file should be something like this:

"move /-y "%1"  "D:\Example\dir1"   >> "D:\Example\dir1\Cmdlog.txt" 2>&1"

Furthermore your scribe service account should have the correct privileges to read & write.

Happy Ol' School integrating!