Is it possible to insert 50 000 record in single statement in BIzTalk Server?

Question

I have 1 flat file it will contains more than 100k rows. I need to entry in table (Dynamic table based on data). so it is possible to insert data in batch wise in BizTalk?

Here is my Biztalk Mapping.

Biztalk project Configuration.(Batch size in 100 still insert single record at a time.)

following biztalk project add single record but need to add multiple line in once statement.

I have checked it will insert one by one record, so it will take to much time. So can anyone please provide the alternative solution for the Biztalk server to handle large data.

Here, Is my text file.

Here, Is my Table structure.

Here, is my Table insert.

Here, is my WCF-SQL Binding.

Here, my trigger to check same.

here my BizTalk Orchestration
Here, is Output of Test mapping.
Here is Receive File Flat File Schema wizard(.txt file).

Attach Receive File .xsd file for more idea.

    <?xml version="1.0" encoding="utf-16"?>
<xs:schema xmlns="http://SplitLargeFileIntoXML.ReceiveFileSchema" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" targetNamespace="http://SplitLargeFileIntoXML.ReceiveFileSchema" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:annotation>
    <xs:appinfo>
      <schemaEditorExtension:schemaInfo namespaceAlias="b" extensionClass="Microsoft.BizTalk.FlatFileExtension.FlatFileExtension" standardName="Flat File" xmlns:schemaEditorExtension="http://schemas.microsoft.com/BizTalk/2003/SchemaEditorExtensions" />
      <b:schemaInfo standard="Flat File" codepage="65001" default_pad_char=" " pad_char_type="char" count_positions_by_byte="false" parser_optimization="speed" lookahead_depth="3" suppress_empty_nodes="false" generate_empty_nodes="true" allow_early_termination="false" early_terminate_optional_fields="false" allow_message_breakup_of_infix_root="false" compile_parse_tables="false" root_reference="ReceiveFileSchema" />
    </xs:appinfo>
  </xs:annotation>
  <xs:element name="ReceiveFileSchema">
    <xs:annotation>
      <xs:appinfo>
        <b:recordInfo structure="delimited" child_delimiter_type="hex" child_delimiter="0xA" child_order="postfix" sequence_number="1" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" />
      </xs:appinfo>
    </xs:annotation>
    <xs:complexType>
      <xs:sequence>
        <xs:annotation>
          <xs:appinfo>
            <groupInfo sequence_number="0" xmlns="http://schemas.microsoft.com/BizTalk/2003" />
          </xs:appinfo>
        </xs:annotation>
        <xs:element name="ISA">
          <xs:annotation>
            <xs:appinfo>
              <b:recordInfo structure="delimited" child_delimiter_type="char" child_delimiter="~" child_order="infix" sequence_number="1" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" />
            </xs:appinfo>
          </xs:annotation>
          <xs:complexType>
            <xs:sequence>
              <xs:annotation>
                <xs:appinfo>
                  <groupInfo sequence_number="0" xmlns="http://schemas.microsoft.com/BizTalk/2003" />
                </xs:appinfo>
              </xs:annotation>
              <xs:element name="ISA_Child1" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="1" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="ISA_Child2" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="2" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="ISA_Child3" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="3" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="ISA_Child4" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="4" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="ISA_Child5" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="5" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="ISA_Child6" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="6" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="ISA_Child7" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="7" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="ISA_Child8" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="8" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="ISA_Child9" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="9" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="ISA_Child10" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="10" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="ISA_Child11" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="11" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="ISA_Child12" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="12" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="ISA_Child13" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="13" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="ISA_Child14" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="14" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="ISA_Child15" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="15" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="ISA_Child16" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="16" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="ISA_Child17" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="17" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="GS">
          <xs:annotation>
            <xs:appinfo>
              <b:recordInfo structure="delimited" child_delimiter_type="char" child_delimiter="~" child_order="infix" sequence_number="2" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" />
            </xs:appinfo>
          </xs:annotation>
          <xs:complexType>
            <xs:sequence>
              <xs:annotation>
                <xs:appinfo>
                  <groupInfo sequence_number="0" xmlns="http://schemas.microsoft.com/BizTalk/2003" />
                </xs:appinfo>
              </xs:annotation>
              <xs:element name="GS_Child1" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="1" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="GS_Child2" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="2" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="GS_Child3" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="3" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="GS_Child4" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="4" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="GS_Child5" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="5" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="GS_Child6" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="6" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="GS_Child7" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="7" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="GS_Child8" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="8" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="GS_Child9" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="9" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="CustomerName">
          <xs:annotation>
            <xs:appinfo>
              <b:recordInfo structure="delimited" child_delimiter_type="char" child_delimiter="=" child_order="infix" sequence_number="3" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" />
            </xs:appinfo>
          </xs:annotation>
          <xs:complexType>
            <xs:sequence>
              <xs:annotation>
                <xs:appinfo>
                  <groupInfo sequence_number="0" xmlns="http://schemas.microsoft.com/BizTalk/2003" />
                </xs:appinfo>
              </xs:annotation>
              <xs:element name="CustomerName_Child1" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="1" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="CustomerName_Child2" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="2" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="CustomerID">
          <xs:annotation>
            <xs:appinfo>
              <b:recordInfo structure="delimited" child_delimiter_type="char" child_delimiter="=" child_order="infix" sequence_number="4" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" />
            </xs:appinfo>
          </xs:annotation>
          <xs:complexType>
            <xs:sequence>
              <xs:annotation>
                <xs:appinfo>
                  <groupInfo sequence_number="0" xmlns="http://schemas.microsoft.com/BizTalk/2003" />
                </xs:appinfo>
              </xs:annotation>
              <xs:element name="CustomerID_Child1" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="1" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="CustomerID_Child2" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="2" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="SubScriptionID">
          <xs:annotation>
            <xs:appinfo>
              <b:recordInfo structure="delimited" child_delimiter_type="char" child_delimiter="=" child_order="infix" sequence_number="5" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" />
            </xs:appinfo>
          </xs:annotation>
          <xs:complexType>
            <xs:sequence>
              <xs:annotation>
                <xs:appinfo>
                  <groupInfo sequence_number="0" xmlns="http://schemas.microsoft.com/BizTalk/2003" />
                </xs:appinfo>
              </xs:annotation>
              <xs:element name="SubScriptionID_Child1" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="1" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="SubScriptionID_Child2" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="2" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="DocumentID">
          <xs:annotation>
            <xs:appinfo>
              <b:recordInfo structure="delimited" child_delimiter_type="char" child_delimiter="=" child_order="infix" sequence_number="6" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" />
            </xs:appinfo>
          </xs:annotation>
          <xs:complexType>
            <xs:sequence>
              <xs:annotation>
                <xs:appinfo>
                  <groupInfo sequence_number="0" xmlns="http://schemas.microsoft.com/BizTalk/2003" />
                </xs:appinfo>
              </xs:annotation>
              <xs:element name="DocumentID_Child1" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="1" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="DocumentID_Child2" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="2" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="MediaCreateDate">
          <xs:annotation>
            <xs:appinfo>
              <b:recordInfo structure="delimited" child_delimiter_type="char" child_delimiter="=" child_order="infix" sequence_number="7" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" />
            </xs:appinfo>
          </xs:annotation>
          <xs:complexType>
            <xs:sequence>
              <xs:annotation>
                <xs:appinfo>
                  <groupInfo sequence_number="0" xmlns="http://schemas.microsoft.com/BizTalk/2003" />
                </xs:appinfo>
              </xs:annotation>
              <xs:element name="MediaCreateDate_Child1" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="1" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="MediaCreateDate_Child2" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="2" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="SubScriptionType">
          <xs:annotation>
            <xs:appinfo>
              <b:recordInfo structure="delimited" child_delimiter_type="char" child_delimiter="=" child_order="infix" sequence_number="8" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" />
            </xs:appinfo>
          </xs:annotation>
          <xs:complexType>
            <xs:sequence>
              <xs:annotation>
                <xs:appinfo>
                  <groupInfo sequence_number="0" xmlns="http://schemas.microsoft.com/BizTalk/2003" />
                </xs:appinfo>
              </xs:annotation>
              <xs:element name="SubScriptionType_Child1" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="1" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="SubScriptionType_Child2" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="2" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element minOccurs="0" maxOccurs="unbounded" name="Details">
          <xs:annotation>
            <xs:appinfo>
              <b:recordInfo structure="delimited" child_delimiter_type="hex" child_delimiter="0x9" child_order="infix" sequence_number="9" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" />
            </xs:appinfo>
          </xs:annotation>
          <xs:complexType>
            <xs:sequence>
              <xs:annotation>
                <xs:appinfo>
                  <groupInfo sequence_number="0" xmlns="http://schemas.microsoft.com/BizTalk/2003" />
                </xs:appinfo>
              </xs:annotation>
              <xs:element name="Details_Child1" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="1" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="Details_Child2" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="2" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="Details_Child3" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="3" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="Details_Child4" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="4" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="GE">
          <xs:annotation>
            <xs:appinfo>
              <b:recordInfo structure="delimited" child_delimiter_type="char" child_delimiter="~" child_order="infix" sequence_number="10" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" />
            </xs:appinfo>
          </xs:annotation>
          <xs:complexType>
            <xs:sequence>
              <xs:annotation>
                <xs:appinfo>
                  <groupInfo sequence_number="0" xmlns="http://schemas.microsoft.com/BizTalk/2003" />
                </xs:appinfo>
              </xs:annotation>
              <xs:element name="GE_Child1" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="1" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="GE_Child2" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="2" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="GE_Child3" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="3" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="IEA">
          <xs:annotation>
            <xs:appinfo>
              <b:recordInfo structure="delimited" child_delimiter_type="char" child_delimiter="~" child_order="infix" sequence_number="11" preserve_delimiter_for_empty_data="true" suppress_trailing_delimiters="false" />
            </xs:appinfo>
          </xs:annotation>
          <xs:complexType>
            <xs:sequence>
              <xs:annotation>
                <xs:appinfo>
                  <groupInfo sequence_number="0" xmlns="http://schemas.microsoft.com/BizTalk/2003" />
                </xs:appinfo>
              </xs:annotation>
              <xs:element name="IEA_Child1" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="1" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="IEA_Child2" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="2" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
              <xs:element name="IEA_Child3" type="xs:string">
                <xs:annotation>
                  <xs:appinfo>
                    <b:fieldInfo justification="left" sequence_number="3" />
                  </xs:appinfo>
                </xs:annotation>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Please try to avoid using words like *lakh* that are not understood globally — James Z, Jan 24 '23 at 15:05
Biztalk isn't an ETL tool. It's a message orchestrator. Those messages are *live* messages, eg a customer posting an order *now* that needs to be booked and billed *now*. Biztalk will send that order to all the systems involved and coordinate the messages between them. Just because Biztalk has mappings and connectors doesn't mean it's an ETL tool like SQL Server's Integration Services. 50K rows in a file is no data for an ETL tool. — Panagiotis Kanavos, Jan 24 '23 at 15:56
@PanagiotisKanavos - can you please let me know used of Batch size in Biztalk is per the Decraption it will store data in memory and then insert into table but currently doing one by one insert so it will take more than 3 minute for 50K record. — Hardik, Jan 25 '23 at 13:11
Don't use Biztalk. It's not an ETL tool. Or use Biztalk to import a CSV with 100K records in 3 seconds by executing a `BULK INSERT` command in SQL Server or whatever the equivalent is in the database you use. — Panagiotis Kanavos, Jan 25 '23 at 13:15
So if you run the map in Visual Studio, do you get multiple rows? If not, can you add your schemas and a sample input XML file to the question? It is very hard to diagnose what is wrong without those. P.S. you probably don't even need an Orchestration for this. — Dijkgraaf, Jan 29 '23 at 09:45
hello @Dijkgraaf - I have Put all the details about Text file and Receive File schema with details. let me know if need more details i will provide project link of git hub so based on that get more idea. — Hardik, Jan 30 '23 at 06:06
For the schema, please don't copy it from the browser, as that puts - signs etc. in, making it invalid. Use Notepad or Notepad++ to open it up and copy it. Also format it as a code block, easiest is to put ``` before and after — Dijkgraaf, Jan 30 '23 at 08:51
Hello @Dijkgraaf - I have change formatting sorry for the Issue can you please help me now as i am not able to insert multiple record in 1 statement in biztalk — Hardik, Jan 30 '23 at 09:43
@Dijkgraaf - I have done Bulk insert but now facing issue is like i need to split record in 272 Column with Tab seprator but biztalk is take 8 minute to split 272 column and also gets hang some time. So can you please provide me any solution for split thoes record. — Hardik, Feb 07 '23 at 06:13
That sounds like a different question. Please post it as a new question providing the appropriate details. — Dijkgraaf, Feb 07 '23 at 20:42

Dijkgraaf · Answer 1 · 2023-01-27T19:42:33.920

1

Yes, it is possible to do.

First off when you generate your database schema make sure you use the TableOp/Insert.

And in your Action of your WCF-SQL or WCF-Custom with sqlbindings have one of the below. The first if are sending multiple different Operations through the same port (then the Operation name has to match that set in the message context, either via Orchestration Logical Port Operation name or BRE rules), or the second you can use if you are only sending one type of payload.

<BtsActionMapping xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Operation Name="InsertCustomer" Action="TableOp/Insert/dbo/CustomerDetails" />
  <Operation Name="InsertPO" Action="TableOp/Insert/dbo/PurchaseOrder" />
</BtsActionMapping>

or

TableOp/Insert/dbo/CustomerDetails

Of course replacing CustomerDetails with whatever table you are trying to insert into.

From the looks of it your map or schema is probably not correct, and if you test it in Visual Studio, you would also probably get a single record, and that is where the problem is.

In this case your schema looks to be wrong as your Rows have a MaxOccur of 1, e.g. 1 line. Try changing that to MaxOccurs *

edited Jan 27 '23 at 19:42

answered Jan 26 '23 at 04:19

Dijkgraaf

11,049
17
42
54

@djkgraaf - I have implimante same code in project but issue is biztalk insert single record in table. so it will take time. so can you please provide any link that help me to insert into Bulk in table. – Hardik Jan 26 '23 at 06:06
@Hardik I've used that exact approach and it works. So it sound sounds like there may be an issue, either with one the schemas or your map. As you've not given details of either it is a bit hard to diagnose. What happens when you use the map in visual studio? Do you get multiple records in the output? – Dijkgraaf Jan 26 '23 at 07:42
I have update my question with all the details can you please help me out. or can you please provide any simple project. – Hardik Jan 27 '23 at 08:12
I have change the maxOccus as well but still it will insert 1 record at time can you please provide any simple project link based on that i will create project as i am begginner in Biztalk so it will helpfull for me. – Hardik Jan 28 '23 at 06:30
Just For adding Only Details(Nodes) contain multiline. so can you please provide solution with details and if mapping is wrong then also let me know what is wrong so based on that i will change it. – Hardik Jan 28 '23 at 07:37

score 1 · Accepted Answer · answered Jul 12 '23 at 10:13

After lots search I am found the Issue in my Mapping We need to create Type Table To Insert multiple record.

For example.

IF type_id('[dbo].[Table_Type]') IS NOT NULL
    DROP TYPE [dbo].[Table_Type];
        
CREATE TYPE [dbo].[Table_Type] AS TABLE(
    [ID] [int] NOT NULL,
    [document_id] [nvarchar](max) NOT NULL,
    [recordgroup] [nvarchar](max) NOT NULL,
    [recordtype] [nvarchar](max) NULL,
    [recordcategory] [nvarchar](max) NULL,
    [tableprefix] [nvarchar](max) NULL,
    [recorddetails] [nvarchar](max) NULL
)
GO

Now Done Mapping

Enter your File path in TestMap Input Instance

Enter TestMap OutPut Instance (Now you can verify your mapping using Test Map)

Your mapping output look like this :

<?xml version='1.0' encoding='UTF - 8'?>
<ns0:Table_TypeMain xmlns:ns0="http://sample.emp">
  <Table_Type>
    <EmpNo>123</EmpNo>
    <EmpName>HP</EmpName>
    <age>26</age>
  </Table_Type>
  <Table_Type>
    <EmpNo>456</EmpNo>
    <EmpName>UV</EmpName>
    <age>30</age>
  </Table_Type>
  <Table_Type>
    <EmpNo>789</EmpNo>
    <EmpName>JK</EmpName>
    <age>32</age>
  </Table_Type>
</ns0:Table_TypeMain>

Then Deploy your application and Set all Value

USe CompositeOperation in Action

Now you can insert multiple record using BizTalk.

Is it possible to insert 50 000 record in single statement in BIzTalk Server?

2 Answers2