Hi,

Currently I have a requirement in my project to import flat file data to multiple SQL tables using BizTalk. As of now I am doing the following steps :

1. Prepare a input schema using flat file schema wizard

2. Prepare a Output schema(creating a SQL Procedure) using WCF-SQL Adapter

3. Map from input schema to output schema

As of now I make multiple calls to the procedure for insertion, Is it possible to perform a bulk insertion into multiple tables ? Is there any other alternative apart from creating a procedure and inserting the data?

Thanks,

Chaithanya

• Edited by Chaithanya_S Thursday, January 22, 2015 3:08 PM

Yes, by using Composite Operations with the WCF SQL Adapter:

https://msdn.microsoft.com/en-us/library/dd788136.aspx

http://social.technet.microsoft.com/wiki/contents/articles/26279.biztalk-streamlining-wcf-sql-and-oracle-messaging-only-and-other-patterns.aspx

Depending on the structure of the incoming data and table configurations, you might also be able to use Table type parameters.

You should also evaluate SSIS if the sources are csv style formats.

• Edited by Johns-305 [boatseller] Thursday, January 22, 2015 3:22 PM

I sent XML as input to stored proc and extracting the node values to save the data into multiple tables. I am facing a problem with this.

This is my input xml schema :

What you're trying here will not work at all.

If you want to send Xml to a Stored Procedure:

1. Create the SP with a parameter of type Xml.  You can have other parameters too of course.
2. Use the Wizard to create a Schema for the Stored Procedure.
3. Map the Xml message as a whole to the Xml Parameter.

The Action really has nothing to do with it, the SP name comes from the Message's Namespace.

• Edited by Johns-305 [boatseller] Saturday, January 24, 2015 2:41 PM

Hi All,

Thanks for your suggestions.

@Abhishek : "You are not passing correct message to SQL for its processing . check your Orchestration whether you are sending the correct transformed SQL message from Biz Talk or not  which is confined to the schema of the SQL."

Mapping Details : 

Please find the details of stored proc below : 

CREATE PROC [dbo].[MyProcedure] 
(
	@XML XML			
) 
AS
BEGIN

SELECT FirstColumn = Col1[1],
       SecondColumn = Col2[1]  
FROM @Xml.nodes('RootNode/Header')

END 

Can you please tell me if I am missing out anything from the above mapping & stored proc details?

• Edited by Chaithanya_S Tuesday, January 27, 2015 7:11 AM Updating

Hi All,

Thanks for all your suggestions. 

I updated the send port configuration at the console with "CompositeOperation" and used looping which worked.

Thanks,

Chai

• Edited by Chaithanya_S Wednesday, January 28, 2015 3:47 PM Update
• Marked as answer by Angie xuMicrosoft contingent staff, Moderator 8 hours 58 minutes ago

Hi All,

Thanks for all your suggestions. 

I updated the send port configuration at the console with "CompositeOperation" and used looping which worked.

Thanks,

Chai

• Edited by Chaithanya_S Wednesday, January 28, 2015 3:47 PM Update
• Marked as answer by Angie xuMicrosoft contingent staff, Moderator Monday, February 02, 2015 2:28 AM