Often, we need to extract records and replicate information from Dynamics CRM to a separate SQL Server Database for various purposes, such as for complex data warehouse analysis and reporting requirements that may involve queries joining with information from other enterprise systems.

If your MS CRM is on-prem, then you can query the database directly using filtered views, or copy the database schema using various functions built into SQL Server Management Studio. If your MS CRM is online, however, you don’t have direct access to the database and will be cumbersome to manually come up with the database and table schemas.

The following C# .NET code allows you to query one or more CRM entity schemas, and generate SQL CREATE TABLE scripts. Hopefully, this saves you from having to handcraft them yourself. Combined with KingswaySoft SSIS Integration Toolkit for Microsoft Dynamics CRM, you can get a local copy of your CRM data in no time.

 

To execute, pass in a CRM on-prem/online connection string, entities of interest (e.g. “account”, “contact”), any prefix (e.g. “crm_”), and SQL script output file path (e.g. “C:\CRM_DB_Schema.sql”).

Below is an excerpt from the generated SQL file. The script drops the existing table if exists before then creates the table for the specified entities (e.g. account and contact). You can open the generated file in SQL Server Management Studio and run it against the database you’d like to store CRM records to. For better readability, the script also includes CRM field display names against the table columns.

 

Now you have tables for CRM account and contact in your local SQL Server database, after which you can use SSIS KingswaySoft SSIS Integration Toolkit for Microsoft Dynamics CRM to select account and contact records to be copied across (e.g. select attributes and active records only).

sql server database schema

sql server database schema 2

 

Another big advantage of using the script above to generate the SQL database table schemas is that the column names match the names in CRM, allowing you to easily map between CRM Input Column and SQL Destination Column.

sql server database schema 3

External links: