Updategram – Updating Multiple rows

Hi,

I ma finding it quite difficult to usew the SQL Adapter to update multiple rows in a table. I have been using BizTalk for over a year and I have managed to Insert, Select, Delete and execute Stored Procedures. But updating multiple rows has gotten the better of me.

I have followed the examples off the net. Where I use the wizard to generate the Updategram. I have imported the Updategram:Id and seemed to be executing the update. Problem is that it just hungs in Active state and nothing happens. No errors are written to the Event Log and the Process on the SQL server has no details and sits in a suspended state.

I am receiving a text file with customers who have had their mail returned to AUS Post. All I want to do is map those customer to the SQL Adapter schema and update the DeadLetter flag in the Customer table.

I had to use

Standard SQL is

update customer set DeadLetter = 1 where customercode in (‘xxxx’,’xxxx’)

Schema:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

<?

 

xml version=“1.0“ encoding=“utf-16“

?>

<

 

xs:schema xmlns:tns=“http://CountryRoad.BizTalk.CRM.DeadLetter“ xmlns:updg=“urn:schemas-microsoft-com:xml-updategram“ xmlns:b=“http://schemas.microsoft.com/BizTalk/2003“ attributeFormDefault=“unqualified“ elementFormDefault=“qualified“ targetNamespace=“http://CountryRoad.BizTalk.CRM.DeadLetter“ xmlns:xs=“http://www.w3.org/2001/XMLSchema“

>

<

 

xs:import schemaLocation=“.\updategram.xsd“ namespace=“urn:schemas-microsoft-com:xml-updategram“

/>

<

 

xs:annotation

>

<

 

xs:appinfo

>

<

 

b:references

>

<

 

b:reference targetNamespace=“urn:schemas-microsoft-com:xml-updategram“

/>

</

 

b:references

>

</

 

xs:appinfo

>

</

 

xs:annotation

>

<

 

xs:element name=“UpdateDeadLetterRequest“

>

<

 

xs:complexType

>

<

 

xs:sequence

>

<

 

xs:element xmlns:updategram=“urn:schemas-microsoft-com:xml-updategram“ updategram:Prefix=“updg“ minOccurs=“1“ maxOccurs=“unbounded“ name=“sync“

>

<

 

xs:complexType

>

<

 

xs:sequence

>

<

 

xs:element updategram:Prefix=“updg“ minOccurs=“0“ maxOccurs=“unbounded“ name=“before“

>

<

 

xs:complexType

>

<

 

xs:sequence

>

<

 

xs:element minOccurs=“0“ maxOccurs=“unbounded“ name=“Customer“

>

<

 

xs:complexType

>

<

 

xs:attribute ref=“updategram:id“

/>

<

 

xs:attribute name=“CustomerCode“ type=“xs:string“

/>

</

 

xs:complexType

>

</

 

xs:element

>

</

 

xs:sequence

>

</

 

xs:complexType

>

</

 

xs:element

>

<

 

xs:element updategram:Prefix=“updg“ minOccurs=“0“ maxOccurs=“unbounded“ name=“after“

>

<

 

xs:complexType

>

<

 

xs:sequence

>

<

 

xs:element minOccurs=“0“ maxOccurs=“unbounded“ name=“Customer“

>

<

 

xs:complexType

>

<

 

xs:attribute ref=“updategram:id“

/>

<

 

xs:attribute name=“CustomerCode“ type=“xs:string“

/>

<

 

xs:attribute name=“DeadLetter“ type=“xs:boolean“

/>

</

 

xs:complexType

>

</

 

xs:element

>

</

 

xs:sequence

>

</

 

xs:complexType

>

</

 

xs:element

>

</

 

xs:sequence

>

</

 

xs:complexType

>

</

 

xs:element

>

</

 

xs:sequence

>

</

 

xs:complexType

>

</

 

xs:element

>

<

 

xs:element name=“UpdateDeadLetterResponse“

>

<

 

xs:complexType

>

<

 

xs:sequence

>

<

 

xs:element name=“Success“ type=“xs:anyType“

/>

</

 

xs:sequence

>

</

 

xs:complexType

>

</

 

xs:element

>

</

 

xs:schema

>

In my map I had to use the iteration as the unique Id as the Customercode is Varchar and has a leading zero and could contain duplicates.

below is the map I’ve used. Am I doing something wrong. I have followed many examples. eg. CustomerCode not in the After, DeadLetter not in the before

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

<?

 

xml version=“1.0“ encoding=“utf-16“

?>

<!–

 

Generated using BizTalk Mapper on Thu, Dec 11 2008 04:29:03 PM

–>

<

 

mapsource Name=“BizTalk Map“ BizTalkServerMapperTool_Version=“2.0“ Version=“2“ XRange=“100“ YRange=“420“ OmitXmlDeclaration=“Yes“ TreatElementsAsRecords=“No“ OptimizeValueMapping=“No“ GenerateDefaultFixedNodes=“Yes“ CopyPIs=“No“ method=“xml“ xmlVersion=“1.0“ IgnoreNamespacesForLinks=“Yes“

>

<

 

SrcTree

>

<

 

Reference Location=“.\returnmailfile.xsd“

/>

</

 

SrcTree

>

<

 

TrgTree RootNode_Name=“UpdateDeadLetterRequest“

>

<

 

Reference Location=“.\updatecustomerservice.xsd“

/>

</

 

TrgTree

>

<

 

ScriptTypePrecedence

>

<

 

CSharp Enabled=“Yes“

/>

<

 

ExternalAssembly Enabled=“Yes“

/>

<

 

VbNet Enabled=“Yes“

/>

<

 

JScript Enabled=“Yes“

/>

<

 

XsltCallTemplate Enabled=“Yes“

/>

<

 

Xslt Enabled=“Yes“

/>

</

 

ScriptTypePrecedence

>

<

 

TreeValues

>

<

 

TestValues

/>

<

 

ConstantValues

/>

</

 

TreeValues

>

<

 

Pages

>

<

 

Page Name=“Page 1“

>

<

 

Links

>

<

 

Link LinkID=“1“ LinkFrom=“/*[local-name()=’&lt;Schema&gt;‘]/*[local-name()=’MAILFILE’]/*[local-name()=’CustomerRecord’]/*[local-name()=’CustomerCode’]“ LinkTo=“/*[local-name()=’&lt;Schema&gt;‘]/*[local-name()=’UpdateDeadLetterRequest’]/*[local-name()=’sync’]/*[local-name()=’before’]/*[local-name()=’Customer’]/@*[local-name()=’CustomerCode’]“ Label=“”

/>

<

 

Link LinkID=“2“ LinkFrom=“1“ LinkTo=“/*[local-name()=’&lt;Schema&gt;‘]/*[local-name()=’UpdateDeadLetterRequest’]/*[local-name()=’sync’]/*[local-name()=’after’]/*[local-name()=’Customer’]/@*[local-name()=’DeadLetter’]“ Label=“”

/>

<

 

Link LinkID=“3“ LinkFrom=“/*[local-name()=’&lt;Schema&gt;‘]/*[local-name()=’MAILFILE’]/*[local-name()=’CustomerRecord’]/*[local-name()=’CustomerCode’]“ LinkTo=“/*[local-name()=’&lt;Schema&gt;‘]/*[local-name()=’UpdateDeadLetterRequest’]/*[local-name()=’sync’]/*[local-name()=’after’]/*[local-name()=’Customer’]/@*[local-name()=’CustomerCode’]“ Label=“”

/>

<

 

Link LinkID=“4“ LinkFrom=“/*[local-name()=’&lt;Schema&gt;‘]/*[local-name()=’MAILFILE’]/*[local-name()=’CustomerRecord’]/*[local-name()=’CustomerCode’]“ LinkTo=“2“ Label=“”

/>

<

 

Link LinkID=“5“ LinkFrom=“2“ LinkTo=“/*[local-name()=’&lt;Schema&gt;‘]/*[local-name()=’UpdateDeadLetterRequest’]/*[local-name()=’sync’]/*[local-name()=’before’]/*[local-name()=’Customer’]/@*[local-name()=’id’]“ Label=“”

/>

<

 

Link LinkID=“6“ LinkFrom=“2“ LinkTo=“/*[local-name()=’&lt;Schema&gt;‘]/*[local-name()=’UpdateDeadLetterRequest’]/*[local-name()=’sync’]/*[local-name()=’after’]/*[local-name()=’Customer’]/@*[local-name()=’id’]“ Label=“”

/>

</

 

Links

>

<

 

Functoids

>

<

 

Functoid FunctoidID=“1“ X-Cell=“62“ Y-Cell=“220“ Functoid-FID=“107“ Functoid-Name=“String Concatenate“ Label=“”

>

<

 

Input-Parameters

>

<

 

Parameter Type=“Constant“ Value=“1“ Guid=“{DB2869C3-4E1E-4030-A5EB-254C359D089C}“

/>

</

 

Input-Parameters

>

</

 

Functoid

>

<

 

Functoid FunctoidID=“2“ X-Cell=“57“ Y-Cell=“213“ Functoid-FID=“474“ Functoid-Name=“Iteration“ Label=“”

>

<

 

Input-Parameters

>

<

 

Parameter Type=“Link“ Value=“4“ Guid=“{F25A8856-281E-4719-A74C-1529821B99D0}“

/>

</

 

Input-Parameters

>

</

 

Functoid

>

</

 

Functoids

>

</

 

Page

>

</

 

Pages

>

</

 

mapsource

>

Any suggestions are mostly welcomed.

Thanks and kind regards

Rob