Normally, when I integrate SQL Server with BizTalk server, I prefer to use stored procedures. This because I believe that a database within an integration scenario should not expose data models but should be a service that exposes methods.
When you need to insert data sets into a SQL Server through a stored procedure, you can use a composite operation or develop a stored procedure that accept a user-defined table type as a parameter.
For more information about the use of a composite operation, refer to the article “https://pellitterisbiztalkblog.wordpress.com/2013/07/01/insert-master-detail-data-structure-on-sql-server-in-a-single-transaction-using-biztalk-server”.
Develop a stored procedure that accepts a user-defined table type as a parameter is very simple. Simply create a user-defined table type as shown below.
|CREATE TYPE dbo.Lines AS TABLE
lineId INT NOT NULL,
lineField1 VARCHAR(10) NULL,
lineField2 VARCHAR(10) NULL,
PRIMARY KEY (lineId)
Then create a stored procedure that uses the type previously created.
|CREATE PROCEDURE dbo.BizTalkSample
@lines Lines READONLY
INSERT INTO sampleHeader
INSERT INTO sampleLines
At this point, it is sufficient to generate a schema from a BizTalk project.
There are different contexts in which it is preferable to use a method or the other. The main difference is that a composite operation invokes the stored procedure several times while a stored procedure that accepts a user-defined table type as parameter is called only once.
If the need is to send a dataset to SQL Server, the second method can introduce significant advantages in terms of performance. The BizTalk message is a few percent smaller, but the main point is that the number of communications between BizTalk Server and SQL Server is drastically reduced and SQL Server performs the transaction much faster.
I conducted several tests using a 100MB file which contained about 8 million lines. The improvement was amazing. The time has decreased significantly from about 20 minutes using a composite operation in just over three minutes by running a single stored procedure that accept a user-defined table type as a parameter.