Using SQL Server Integration Services packages to generate reports from CRM Online

One of our local Microsoft Gold Certified Partners uncovered a great way to generate reports from CRM Online outside of the out-of-the box mechanisms.  Thanks Jeff!  If you are interested in learning more you can contact them at:

Phone: 206.686.8704

Email: info@madronasg.com

CRM online represents a fantastic way to use Dynamics for customers who don't want an on premise installation.  That said, for many organizations the reporting wizard and Excel are not enough to support the full scope of their needs.

One option is to replicate the data locally so that custom reports and dashboards can be used against the core online CRM data.

There are multiple approaches that can be used to solve this problem, but today I'll share one simple approach that only requires an organization use an existing standard SQL Server to both host the data retrieval components (SQL Server Integration Services packages) and to maintain the replicated data.

Here's a rough overview of how the process can work -

1.  The SSIS job will contain a step within which a custom assembly will be called that will connect to the online crm webservices, and the resulting returned data will be an xml file for the relevant CRM entity.

2.  The SSIS job will contain a setup that will format this data and load the extract into a replica of the CRM filtered view for the relevant entity.

3.  The core CRM security will be applied to the filtered view so that users will have the same permission against the replica data that they maintain against the online application.

The result is a set of replica views and tables that are refreshed at a scheduled interval, and then these views can be queried and reported off of just as on premise reporting is done today to deliver dashboards or OLAP reports.  This approach is one that I've used in production today with CRM Online and has worked well for the past couple of months.

The final outcome is a set of compelling reports and dashboards that users will access from within Microsoft Dynamics CRM Online. Some examples are posted below –