For lotsa people this won't be too interesting but if you are a BizTalker and have regular interaction with a SQL Server keep on reading.
First I wish to thank Richard Seroter who had an excellent post about the SQL adapter.(see here). (Don't know why i havent seen his blog before this guy is posting interesting stuff). Via Richards Blog you automatically also end up at Stephen Kaufman's Blog, cause he has an excellent article about the at-identity.
Whenever I used the SQL adapter I hated it that the SQL adapter would generate an Orchestration and a Schema. Cause whenever I used the SQL Adapter Schema Wizard I don't want the port in a NEW orchestration but in my CURRENT orchestration. This is where Richards Excellent article comes in (see here) . Just define a Project called 'SQL Something Base', and define all your SQL generated schemas in that project make the ports of the orchestrations public and presto. Use it wherever you like. I really like the idea. Also whenever a table should change you dont have to go over the process of copying the Multipart Message and Ports.
The Second part of his post talks about the possibillity to make a Update / Insert / Delete for a table with an UpdateGram. But when i tried this it wouldn't work as expected. So i messed around a littlebit and i think i have found the answer to a good Working Update / Insert / Delete. Lots of it is copied from Richards original idea but still here we go. If you don't know updategrams read this first.
I have a table in SQL, and the table looks like this :
CREATE TABLE [dbo].[Customers] (
[CustomerID] [int] IDENTITY (1, 1) NOT NULL ,
[CompanyName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ContactName] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ContactTitle] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
So when I run the SQL Adapter schema Wizard i get a UpdateGram looking like this. ( well not exactly, note the field updg:id)
So what about the updg:id. Well have a read of this article about the use of id in an updategram. Then you can use the trick as described by Stephen Kaufman to create the id Attribute. In short the updg:id is used to tell SQL wich before block belongs to wich after blok. And this is where Richard Seroters article was wrong. It used the key of the record for the ID. But that makes it impossible to update the same record twice in the same run. So I followed all of his steps but my map looks a littlebit different. have a look at it.
I use the iteration to generate a unique key for me. So now I can update the same record in one document. The second page looks like this :
notice the ID field is not maopped because it is a identity.
I know I had to steal and borrow a lot, and i didn't invented it myself. I wanted to tell you about my findings and hope you like it.