FetchXML is a proprietary query language that is used to retrieve data from a CDS/CRM instance. However, FetchXML query is not as flexible nor extensive as an SQL query and has its own restrictions, which we discussed in a previous blog post (Limitations with CRM FetchXML). Considering these limitations, most users prefer to read CDS/CRM data by connecting to the CDS/CRM database directly, where they can build the query with their most familiar query language – SQL query, and easily fetch data with complex joins. But when it comes to CRM Online and CDS environments, the database accessibility is no longer available requiring users to either use FetchXML query or build the OData query through a WebAPI connection to be able to work with the CDS/CRM Online data. Microsoft has recently introduced the read-only SQL data connection for CDS/CRM Online as a preview feature. Further details about this feature can be found on the corresponding Microsoft documentation page.
In this blog post, we will demonstrate how to connect to the CDS/CRM Online SQL database through an ADO.NET Connection Manager and query data using the Premium ADO NET Source component, a part of our SSIS Productivity Pack.
Enabling TDS Endpoint Feature
To enable the TDS endpoint, you must sign in to the Power Platform Admin center, then head to Environments > [select an environment] > Settings > Product > Features.
Make sure you have the System Administrator or System Customizer security role or equivalent permissions to enable the TDS endpoint.
Configuring the ADO.NET Connection Manager
The SQL data connection becomes available once the TDS endpoint is enabled. You can now configure the ADO.NET Connection Manager in SSIS to establish the database connection to the CDS/CRM Online instance. You will need SSDT version 15.9.0 or later in Visual Studio 2017 or Visual Studio 2019, which has the Azure Active Directory authentication support to establish the Common Data Service endpoint SQL connection.
The screenshot below shows an example of the ADO.NET Connection Manager configuration. The server name is the organization address URL followed by a comma and the port value of 5558.
Note the database discovery is not supported at this moment, so you need to manually specify the database name, which is the CDS/CRM organization name in this case.
Querying Data Using ADO.NET Source Component
Reading can be done through the out-of-box ADO.NET Source component or our Premium ADO.NET Source component. In this article, we will use our Premium ADO.NET Source as it offers advanced options and functionality. The database table discovery service is also not available at this moment, so you need to build your own SQL query and use the <Custom Command> option in Premium ADO.NET Source component.
With the use of SQL query, you can easily perform JOIN on N:N relationship entities, which is restricted in FetchXML.
Closing Note
When faced with limitations of FetchXML, developers can querying CDS/CRM Online data using SQL query instead, thus providing much better performance and more flexibility to perform filtering and join entity actions.