Headerbild

eBizTalk - Berichten, Unterhalten, Weiterbilden

Ob Branchendiskurs, Fachartikel oder ein Blick hinter die Unternehmenskulissen: eBizTalk ist die Plattform, die uns auffordert, unsere Leser über Events und Projekte auf dem Laufenden zu halten. Und uns gegenseitig natürlich auch.

Stored Procedure mit Send Port aufrufen

Veröffentlicht am 21.08.2019 von Clemens Kruse , BizTalk , Integration , Pattern

Im Rahmen eines unserer Kundenprojekte ergab sich die Notwendigkeit, aus dem BizTalk heraus unsere SQL Database mit XML Strukturen zu befüllen. Eine entsprechende Stored Procedure mit der Bezeichnung "SpFillTableFromXML" existiert in der Datenbank, die eine XML-Datei entgegennimmt, um deren Felder auszulesen und deren Werte in eine äquivalente Datenbanktabelle einzutragen.

Wie die SP genau funktioniert, ist nicht relevant. Aus BizTalk-Sicht interessiert uns nur, dass wir sie aufrufen müssen, und der Body der aktuellen Message als Parameter übergeben werden soll.

image

Auf den ersten Blick bietet die Dokumentation von Microsoft eine Anleitung zu genau dem Thema und fordert dazu gerade mal sieben Minuten Lesezeit: https://docs.microsoft.com/en-us/biztalk/adapters-and-accelerators/adapter-sql/execute-stored-procedures-with-a-single-xml-parameter-in-sql-using-biztalk

Auf den zweiten Blick spart Microsoft nicht nur an Minuten, sondern auch an Details. Sich durch die Querverweise zu kämpfen, hilft dabei nicht bis zum Schluss.

Daher nun die Schritt-für-Schritt-Anleitung, um ohne Umwege zum Ziel zu kommen:

Setup

  • BizTalk Server 2016
    • Receive Port für Entgegennahme der XML-Datei
    • WCF-Custom Send Port für Aufrufen der SP
  • MS SQL Server 2014
  • SpFillTableFromXML(xml)
  • Tabelle in die von der SP hineingeschrieben wird

clip_image002

Vorgehen: „Mal schnell den Send Port erstellen“

  • InvokeStoredProcedureThatTakesXml
  • WCF-Custom
  • TypedProcedure/dbo/SpFillTableFromXML

Wir erstellen einen neuen Static One-Way Send Port mit dem ausdrucksstarken Namen "InvokeStoredProcedureThatTakesXml":

image

Dieser ist vom Typ "WCF-Custom". Die "PassThruTransmit" Pipeline genügt für uns.

Klick auf [Configure…] > Wir tragen die Uri zu unserer Datenbank ein (Syntax: mssql://[Server]/[InstanceName]/[Database]), sowie die nötige Action:

clip_image002

Als nächstes legen wir das [Binding] fest:

clip_image004

Und ausgehend davon, dass BizTalk sich mit seinem Windows-User am SQL Server authentifizieren soll, lassen wir bei den [Credentials] alles frei:

clip_image006

Anschließend legen wir unter [Messages] nur noch das Template fest:

clip_image008

Ist doch easy, oder? Der Teufel steckt im Detail.

Offensichtlich sind die folgenden Fragen:

  1. Wo bekomme ich denn die Action "TypedProcedure/dbo/SpFillTableFromXML" überhaupt her und
  2. was genau hat es mit der Template-Option unter "Outbound WCF message body" auf sich? Was ist <ns0:XML> (bzw. <xml_info> in der Microsoft-Doku)

Zweck dieses Artikels ist es, genau diese Fragen zu beantworten.

1. [General] > [SOAP Action Header] > [Action] richtig befüllen

Einen Hinweis auf die erste Frage bekommt man unter Punkt 8, der oben genannten Microsoft-Dokumentation:

clip_image010

Folgt man dem link, dann gelangt man zu…

clip_image012

Und schließlich zu…

clip_image014

Aha. Das Schema für die korrekte Action lautet also

TypedProcedure/[SCHEMA]/[STRNG_SP_NAME]

Und in unserem Fall lautet dann die konkrete Action

TypedProcedure/dbo/SpFillTableFromXML

[SCHEMA] und [STRNG_SP_NAME] kann man via MS SQL Management Studio ablesen:

clip_image016

2. [Messages] > [Outbound WCF message body] > [Template] richtig einsetzen

Folgendes haben wir in dem Feld eingetragen

<ns0:SpFillTableFromXML xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo">

<ns0:XML>

<bts-msg-body xmlns="http://www.microsoft.com/schemas/bts2007" encoding="string"/>

</ns0:XML>

</ns0:SpFillTableFromXML>

Hier die Details

Das XML Template besteht inhaltlich aus zwei Teilen:

  1. Grundstruktur der Stored Procedure (Name der SP + Bezeichner des Parameters)
  2. Platzhalter für den Payload, die XML-Datei, die wir der SP als Parameter übergeben

Hinweise

Erklärung

Das Template ist dafür vorgesehen, genau diese Art von Stored Procedures aufzurufen. Nämlich solche, die eine XML als einzigen Parameter entgegennehmen, um dann Dinge zu tun.

Nun bin ich zwar zuversichtlich, dass es in den meisten Fällen gut gehen wird, die markierten Felder von oben durch die eigenen Werte zu ersetzen. Es gibt aber noch eine todsichere Variante, um garantiert die korrekte Template-Action-Kombination zu erzeugen, um Fehler wie diesen hier zu vermeiden:

"... It will be retransmitted after the retry interval specified for this Send Port. Details:"System.Data.SqlClient.SqlException (0x80131904): Procedure or function 'SpFillTableFromXML' expects parameter '@XML', which was not supplied."

Template und Action mit Visual Studio generieren

Zwar findet man in der Dokumentation von Microsoft den folgenden Hinweis:

clip_image018

Das konkrete Vorgehen, inklusive korrekter Abbiegungen fehlt jedoch, und wird deswegen im Folgenden erläutert.

Dazu begeben wir uns ins Visual Studio

[Rechtsklick auf Schema-Projekt] > [Add] > [Add Generated Items…]

clip_image020

hier wählen wir [Consume Adapter Service]:

clip_image022

Falls die Option fehlt, muss das WCF LOB Adapter SDK installiert werden (eine Anleitung dazu hier). Im Wizard führen wir die folgenden Schritte aus:

clip_image002[4]

  1. "sqlBinding" auswählen
  2. Datenbank-URI eintragen (dieselbe, wie im Send Port)
  3. Unter "StronglyTyped Procedures" wählen wir unsere SP "SpFillTableFromXML" aus
  4. Und fügen Sie mit [Add] zu den items hinzu, zu denen ein Schema erstellt werden soll
  5. Abschließend klicken wir auf [OK]

Nun finden wir unter anderem das Schema "TypedProcedure.dbo.xsd" in unserem Projekt. In diesem Schema finden wir übrigens auch die nötige Action für unseren Send Port:

clip_image004[4]

Um nun an das Template zu kommen, generieren wir eine XML-Instanz vom Schema "TypedProcedure.dbo.xsd"…

clip_image006[4]

clip_image008[4]

Und passen diese Instanz entsprechend dem Hinweis von Microsoft an…

clip_image010

Sodass wir das Template für unseren Send Port erhalten:

<ns0:SpFillTableFromXML xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo">

<ns0:XML>

<bts-msg-body xmlns="http://www.microsoft.com/schemas/bts2007" encoding="string"/>

</ns0:XML>

</ns0:SpFillTableFromXML>

google_about_ebiz fb_about_ebiztwitter_about_ebizxing_about_ebiz