Using BizTalk with SQL Databases

Name: Using BizTalk with SQL Databases
Rating: 4.5 (31 reviews)

Using BizTalk WCF-SQL (and older SQL Adapter)

Created byNeal Walters

Last updated 8/2022

English

What you'll learn

BizTalk SQL Polling - Trigger BizTalk to run when data is added or flagged in SQL
Calling BizTalk Stored Procs
BizTalk old SQL Adapter (BizTalk 2016 and before, not available in BizTalk 2020)
BizTalk - WCF-SQL Adapter
Using User-Defined-Tables (UDTS) with BizTalk
Request-Response (Two-Way) Send Ports
BizTalk Orchestration and Maps related to SQL
Polling Options (PollWhileDataFound, AmbientTransaction, PollingInterval)
Multipart Message-Types (Used by Old SQL Adapter)
Passing Complex One-to-Many Data Structures to a Stored Proc using UserDefinedTypes

Course content

6 sections • 51 lectures • 8h 4m total length

Video Walkthrough of Course Outline4:44
WCF-SQL Adapter - The Big Overview6:41
WCF_SQL Architecture Diagram1:27
Why I Use Total Commander In All the Course Videos6:50
Course Downloads0:06

Polling Introduction (Slide Presentation)9:59
Add columns to Table to be Polled4:29
Polling Tracking Table2:04
Create Stored Proc (and Test)16:51
MS-DTC and SQL Configuration Proper Setup3:15
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 Schema from Stored Proc10:36
Clarification of Namespace2:12
Deploy Project/Application Ports, First Testing/Debugging18:22
Deploy a BizTalk project, sign assemblies, and import bindings, then configure a receipt port and receive location with polling, testing, and debugging steps.
Side Note: How to Turn on Tracking1:57
Section Quiz: Polling - Retrieving Data from SQL
Adding a different output schema and a Map12:33
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.
Impact of Top X and PollWhileDataFound20:09
Impact of Ambient Transaction7:24
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 BiztalkPollingHistoryTable10:07

Introduction to User-Defined Tables, Create the Reservation UDT13:55
Creating the FlightReservation UDT6:50
Populate the UserDefinedTable Variables13:53
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 UDTs25:42
SQL to Stored Proc5:30
Create Schemas and Map8:42
Create Orchestration13:32
Setup Ports and Test Happy Path22:55
Show Exception, Set Retries4:39
Quiz: Call Stored Procedure (with User Defined Table as Argument)
Catch Exception7:03
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.
Revised Stored Proc to Return Error as Data - Part 119:05
Revised Stored Proc to Return Error as Data - Part 210:52

BT2016 SQL Adapter - Polling Schema, Orchestration14:23
BT2016 SQL Adapter - Polling Ports and Testing - Part 211:00
Explore how the sql adapter polls data every 30 seconds and pulls customer records, diagnosing duplicates and verifying updates with tracked messages.
BT2016 SQL Adapter - Polling Ports and Testing - Part 19:53
Call Stored Proc (update or Query) - Schema & Orchestration - Part 110:14
Call Stored Proc (update or Query) - Schema & Orchestration - Part 212:54
BT2016 SQL Adapter - Call Stored Proc (update or Query) - Ports and Testing12:52
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.
Overview of UpdateGrams (with C#)6:32
BT2016 SQL Adapter - UpdateGram- Schema/Orchestration12:01
BT016 SQL Adapter - UpdateGram - Ports/Testing6:39

Alternative to Polling - Introduction (Slide Presentation)2:08
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.
Overview of "For XML"9:46
Explore for XML in SQL within BizTalk contexts, comparing raw, auto, explicit, and path modes, and learn to shape output as attributes or elements.
Sample PowerShell using For XML to File5:54
PowerShell to Call Polling Stored Proc8:53
Scheduled Task - PowerShell to XML File6:24
BizTalk Process7:44

Requirements

Must have some prior knowledge of BizTalk (schemas, orchestrations, maps, send/receive ports, etc)
Must have some prior knowledge of SQL queries, updates, inserts, and stored procedures

Description

This course is for developers with understanding of BizTalk basics. Learn how to read and write data to and from Microsoft SQL Server databases. Learn how to generated schemas, call stored procedures to retrieve and update/insert data in SQL.

Learn how BizTalk SQL Polling works, and how to tune the polling parameters for you business needs. Polling allows BizTalk to start an orchestration when certain data appears in the database.

Learn the WCF-SQL Adapter available since at least BizTalk 2010, and learn the older BizTalk SQL Adapter that can be found in older releases (but was removed in BizTalk 2020).

BizTalk orchestrations have the ability to send XML messages to Microsoft SQL server (and other databases) using the WCF-SQL Adapter. A two-way send port is used, basically sending a request and receiving a response.

This course also shows how to use User Defined Tables to send multiple rows of data to and from stored procedures, allow more efficient bulk transactions when needed.

WCF-SQL Architecture, Polling SQL via Receive Locations, how to create an XSD schema from a stored procedure, using tracking.

The course also covers the old BizTalk SQL adapter which is deprecated, just in case you are called upon to upgrade an older system. This section includes both stored procedures and something called "UpdateGrams".

Who this course is for:

Students who have taken my "Real World BizTalk 2020 for Developers" and want to know how to work with SQL data from BizTalk
BizTalk developers that want to know how to work with SQL data from BizTalk

Using BizTalk with SQL Databases

What you'll learn

Explore related topics

Course content

Introduction5 lectures • 20min

Polling - Retrieving Data from SQL13 lectures • 2hr

Call Insert/Update Stored Proc (Simple Arguments)6 lectures • 54min

Call Stored Procedure (with User Defined Table as Argument)12 lectures • 2hr 33min

Old SQL Adapter (not in BizTalk 2020)9 lectures • 1hr 36min

Alternative to Polling6 lectures • 41min

Requirements

Description

Who this course is for: