Business Integration Solutions Documentation

About: SQLREADER Endpoint

In Business Integration Solutions, use the SQLREADER endpoint to read XML messages from Microsoft SQL Server.

The SQL Reader endpoint is used to read data from Microsoft SQL Server, to be processed through a pipeline into Dynamics BC. The reader uses either an automatically generated query or a user-supplied stored procedure to retrieve the data. The query can be parameterized with specific filters. The data is retrieved from SQL Server in XML format, according to an external document definition. The endpoint is configured with this external document definition, a SQL Server connection string and optionally with a user-supplied stored procedure and additional filters. Furthermore the reader endpoint can perform XML validation on the generated XML document.

The SQL Reader endpoint is intentionally kept simple. It does not perform DDL operations on the database, such as loading assemblies, creating tables, alter database properties etc. If advanced features or complex queries are required, they can be defined in SQL Server directly and utilized from the SQL Reader endpoint using the custom stored procedure path. In the following sections the variants of SQL Reader endpoint are described.

Reader with generated query

The SQL Reader with an automatically generated query is the easiest setup. The external document definition, which is an annotated XML Schema, contains a number of tables, fields and relations in a hierarchical structure. From this definition a SQL query is derived that queries and joins these tables to generate an XML document. The generation utilizes the SQL Server XML capabilities (see Microsoft TechNet SQL XML query syntax for details). The query can be parameterized with additional clauses per table, which are specified as full SQL WHERE clauses. See the How-To section for examples.

The XML structure that is generated is defined as follows:

  • The root node is a ti:document element, which serves as a container for the actual data. It will be automatically removed during mapping.
  • Each table, represented as a complex type in the XML Schema, defines the XML element that is generated for its records. The name of the complex type is the XML element tag. The ti:datatable attribute is the SQL Server table name.
  • Each field, represented as a simple type in the XML Schema, defines the XML element that is defined for a column in the record. The name of the simple type is the XML element tag. The ti:datafield attribute is the SQL Server column name.

Reader with user supplied stored procedure

In this situation the endpoint is configured with a SQL Server stored procedure that generates XML. It is the responsibility of the user to define the queries with the correct tag names and ti:document namespace. The stored procedure can be parameterized. During execution these parameters will be passed to the SQL stored procedure. See the How-To section for examples.

The intended uses of the SQL Reader endpoint with stored procedures are:

  • A case where a complex query must be constructed that is beyond the capabilities of automatic query generation.
  • A case where additional data processing must occur, for example marking records for export, or status management.
  • A case where the SQL Server Change Log must be utilized, for example when only changed records must be exported.

You can connect to SQL Server using a connection string. There are various options for defining connection strings, depending on naming, access permissions and other properties. See the Connection Strings web site for many examples.

SQL database ➡️ [SQL Reader] ➡️ [<Activities>] ➡️ [Microsoft Dynamics 365 Business Central]