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 reads data from Microsoft SQL Server for processing through a pipeline into Dynamics BC. The reader uses either an automatically generated query or a user-supplied stored procedure to retrieve the data. You can parameterize the query with specific filters. The data retrieves from SQL Server in XML format, according to an external document definition. Configure the endpoint with this external document definition, a SQL Server connection string, and optionally a user-supplied stored procedure and additional filters. The reader endpoint can also 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, or altering database properties. For advanced features or complex queries, define them in SQL Server directly and use them from the SQL Reader endpoint via the custom stored procedure path. The following sections describe the variants of the SQL Reader endpoint.

Reader with generated query

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

The generated XML structure is defined as follows:

  • The root node is a ti:document element, which serves as a container for the actual data. Mapping automatically removes it.
  • Each table, represented as a complex type in the XML schema, defines the XML element 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 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 setup, configure the endpoint with a SQL Server stored procedure that generates XML. The user is responsible for defining the queries with the correct tag names and ti:document namespace. You can parameterize the stored procedure. During execution, BIS passes these parameters to the SQL stored procedure. See the How-To section for examples.

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

  • Cases where a complex query is required that exceeds the capabilities of automatic query generation.
  • Cases where additional data processing must occur, for example, marking records for export or managing status.
  • Cases where the SQL Server change log must be used, for example, when only changed records need to export.

You can connect to SQL Server using a connection string. Various options exist for defining connection strings, depending on naming, access permissions, and other properties. See the Connection Strings website for examples.

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