Using BizTalk with SQL Databases

Explore how polling triggers BizTalk jobs from SQL data, with interval and service window tuning, top-n retrieval, and inbound versus outbound bindings to start orchestrations.

Learn how to modify a sql airports table to track when BizTalk polls it by adding a nullable polled date time column and preparing to call a polling stored procedure.

Create a polling tracking table for BizTalk with an identity field, identity specification, primary key, and add date time for a polling history table.

Create and test a stored procedure to poll airports, including select, insert, and update operations, plus using a temporary table and transaction control.

Configure the Microsoft distributed transaction coordinator (MSDTC) for SQL by adjusting security, enabling TCP/IP, disabling shared memory, and restarting MSDTC, SQL Server, and the Enterprise single sign-on service.

Create a new Visual Studio BizTalk project, generate an adapter service for a SQL database, configure inbound polling via a stored procedure, and auto‑generate inbound schemas and binding files.

Explore how the namespace for typed polling is formed, showing that the end includes your id, such as a dummy name or John Doe, and why it must be unique.

Deploy a BizTalk project, sign assemblies, and import bindings, then configure a receipt port and receive location with polling, testing, and debugging steps.

Enable tracking for the BizTalk pipeline by opening all artifacts, selecting pipelines, turning on tracking for the SML receive, then verify with track message events in the BizTalk administration console.

Create a new output schema called new airports, define a repeating airport item, and map fields to produce user-friendly airport data. Deploy, refresh, and validate two new airport records.

Learn how to manage polling and data flow in BizTalk with SQL databases, including when to separate data, throttle and debatch, and implement data available checks with stored procedures.

Explore how ambient transactions affect polling in BizTalk's SQL integrations, showing how enabling ambient transactions changes which stored procedures run and how polling data is retrieved in a transactional context.

Prune the BizTalkPollingHistory table by deleting old rows with dateadd and getdate, keeping seven days of data, and implement a nightly SQL Agent job to trim in batches.

Demonstrates upsert logic in a stored procedure for airports, updating or inserting by airport code. Uses created and updated timestamps and passes a pseudo user from orchestration.

Generate a schema from a stored procedure using strongly typed procedures and line of business adapters, configuring outbound data and mapping fields.

Create a map from update airports to the new airports schema, deploy the artifacts, and test with input map instances to validate inserts and updates.

Configure BizTalk receive ports and receive locations for file intake, set up a send port and bindings, enable tracking, and compare orchestration versus pipeline approaches for sql-based messaging.

Learn to build an orchestration in BizTalk that calls a stored procedure by configuring one-way and two-way ports, messages, schemas, maps, and request-response handling.

Create a Reservation UDT in SQL Server, map to Customer and Airline tables, and load a flight reservation from an SML file through BizTalk, using a stored procedure call.

Create the flight reservation UDT to model a junction between reservations and flight legs, using flight and leg sequences and flight number lookups, and flatten to two UDP tables.

Rewrite code into a stored procedure that uses user defined table types, test with temporary tables and variables, and validate flight reservation data before wrapping it as a stored procedure.

Create test sql to process the UDTs using a cursor to loop over reservations, perform lookups by rapid rewards, and manage transactions in a BizTalk SQL integration.

Turn a disk-based flight reservation script into a stored procedure by defining parameters and table variables, wrapping in a transaction, testing, and generating a BizTalk schema.

Create a schema for a stored procedure in BizTalk, configure a binding, then build a map to connect the schema to a typed procedure, preparing for orchestration.

Learn how to create a BizTalk orchestration for a flight reservation, including inbound and two-way messages, schema selection, maps, transforms, and binding for deployment.

Bind the orchestration to a receipt port and receive location. Test the happy path with a sample flight reservation inserted into the database.

Learn how to test and configure the BizTalk send port retry mechanism and handle duplicates and concurrency errors during integration with SQL databases.

Add a scope around the send and receive, create an exception handler, catch a dot net exception rather than a system exception, and handle the exception in the expression shape.

Modify the stored proc to return a single result set with a success flag and error message. Wrap subsequent logic in an else branch to ensure one output.

Learn how to revise a stored procedure to return a single row with a success flag and an error message, and extract it with XPath in a BizTalk SQL workflow.

Configure the older SQL adapter for polling in BizTalk and compare it with the WCF-based adapter. Review generated schemas and the automatically created orchestration, including multipart messages.

Explore how the sql adapter polls data every 30 seconds and pulls customer records, diagnosing duplicates and verifying updates with tracked messages.

Set up a BizTalk 2016 SQL adapter polling flow by creating a receive location and port, binding the schema, configuring a stored procedure, and enabling tracking and security testing.

Learn to call a stored procedure to retrieve a customer by id, test with parameters, generate a schema and orchestration in BizTalk, including elements vs attributes and target namespace.

Learn to design a BizTalk call to a stored procedure (update or query) with schema and orchestration, including request and response messages and two-way ports.

Configure a BizTalk orchestration to call a SQL stored procedure with a customer ID, return a single customer, and test ports, bindings, and inbound/outbound files.

Explore update grammes in BizTalk with SQL databases, using C# and Visual Studio, and learn how before and after images govern insert, update, and delete actions via XSLT mappings.

Set up a fresh BizTalk project to configure a SQL updategram, build a schema and orchestration, map input data, and deploy with request and response ports.

Test the UpdateGram SQL adapter by creating a sample inbound file, configuring ports, generating and replacing dummy data, deploying the orchestration, and verifying success with a query in BizTalk 2016.

Explore alternatives to polling in BizTalk, including Windows Task Scheduler trigger files, non-polling stored procedures, and C# or PowerShell jobs that generate data files, with DMZ deployment considerations.

Explore for XML in SQL within BizTalk contexts, comparing raw, auto, explicit, and path modes, and learn to shape output as attributes or elements.

Demonstrates calling a SQL scalar via a PowerShell script, formats the result with the SML writer, and saves the XML output to a file for easy reading.

Learn to call a polling stored procedure from PowerShell, set a unique namespace and root element for pulled airports, and generate time-stamped output files suitable for scheduling.

Schedule a PowerShell script with Windows Task Scheduler, organizing tasks under megacorp, configuring triggers and actions, using a service account, and enabling history for production deployment.

Integrate a new PowerShell workflow into a BizTalk project by generating a schema from the file name and mapping polling to new airports. Deploy project and verify files are processed.