Using Biztalk With Sql Databases

Using BizTalk WCF-SQL (and older SQL Adapter)

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

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".

Overview

Section 1: Introduction

Lecture 1 Video Walkthrough of Course Outline

Lecture 2 WCF-SQL Adapter - The Big Overview

Lecture 3 WCF_SQL Architecture Diagram

Lecture 4 Why I Use Total Commander In All the Course Videos

Lecture 5 Course Downloads

Section 2: Polling - Retrieving Data from SQL

Lecture 6 Polling Introduction (Slide Presentation)

Lecture 7 Add columns to Table to be Polled

Lecture 8 Polling Tracking Table

Lecture 9 Create Stored Proc (and Test)

Lecture 10 MS-DTC and SQL Configuration Proper Setup

Lecture 11 Create Schema from Stored Proc

Lecture 12 Clarification of Namespace

Lecture 13 Deploy Project/Application Ports, First Testing/Debugging

Lecture 14 Side Note: How to Turn on Tracking

Lecture 15 Adding a different output schema and a Map

Lecture 16 Impact of Top X and PollWhileDataFound

Lecture 17 Impact of Ambient Transaction

Lecture 18 Prune BiztalkPollingHistoryTable

Section 3: Call Insert/Update Stored Proc (Simple Arguments)

Lecture 19 Stored Proc - example, Insert/Update New Airport

Lecture 20 Create Schema from Stored Proc

Lecture 21 Create Map and Deploy

Lecture 22 Port Setup

Lecture 23 Orchestration

Lecture 24 Testing

Section 4: Call Stored Procedure (with User Defined Table as Argument)

Lecture 25 Introduction to User-Defined Tables, Create the Reservation UDT

Lecture 26 Creating the FlightReservation UDT

Lecture 27 Populate the UserDefinedTable Variables

Lecture 28 Create Test SQL to Process the UDTs

Lecture 29 SQL to Stored Proc

Lecture 30 Create Schemas and Map

Lecture 31 Create Orchestration

Lecture 32 Setup Ports and Test Happy Path

Lecture 33 Show Exception, Set Retries

Lecture 34 Catch Exception

Lecture 35 Revised Stored Proc to Return Error as Data - Part 1

Lecture 36 Revised Stored Proc to Return Error as Data - Part 2

Section 5: Old SQL Adapter (not in BizTalk 2020)

Lecture 37 BT2016 SQL Adapter - Polling Schema, Orchestration

Lecture 38 BT2016 SQL Adapter - Polling Ports and Testing - Part 2

Lecture 39 BT2016 SQL Adapter - Polling Ports and Testing - Part 1

Lecture 40 Call Stored Proc (update or Query) - Schema & Orchestration - Part 1

Lecture 41 Call Stored Proc (update or Query) - Schema & Orchestration - Part 2

Lecture 42 BT2016 SQL Adapter - Call Stored Proc (update or Query) - Ports and Testing

Lecture 43 Overview of UpdateGrams (with C#)

Lecture 44 BT2016 SQL Adapter - UpdateGram- Schema/Orchestration

Lecture 45 BT016 SQL Adapter - UpdateGram - Ports/Testing

Section 6: Alternative to Polling

Lecture 46 Alternative to Polling - Introduction (Slide Presentation)

Lecture 47 Overview of "For XML"

Lecture 48 Sample PowerShell using For XML to File

Lecture 49 PowerShell to Call Polling Stored Proc

Lecture 50 Scheduled Task - PowerShell to XML File

Lecture 51 BizTalk Process

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