none
Upload an excel file to SQL using BizTalk SQL Adapter RRS feed

  • Question

  • Hi,

    We have a requirement of reading an excel input file from a location and uploading its data to a SQL table using BizTalk.

    I have created and made use of a custom pipeline component to convert an input excel into the xml format as below.

    <RootNodeName>
      <Header>
        <Coulmn1>12345</Coulmn1>
        <Coulmn2>A1</Coulmn2>
      </Header>
      <Header>
        <Coulmn1>123456</Coulmn1>
        <Coulmn2>A2</Coulmn2>
      </Header>
      <Header>
        <Coulmn1>1234567</Coulmn1>
        <Coulmn2>A3</Coulmn2>
      </Header>
    </RootNodeName>

    My requirement is, I will already have a database table (say "Table1" with column1 and column2). I have to match the "Column1" value from XML with SQL table and update the value for "Column2" for that record in the SQL table.

    I think, next step should be reading this xml and saving it into database table using SQL adapter. However, i don't have much knowledge on using SQL adapter. 

    So, can anyone please help me out on how to use the SQL adapter and further process? Should i write a stored procedure for this purpose? 


    Anand

    Saturday, January 10, 2015 6:56 AM

Answers

  • Yes. Write a stored procedure which takes xml as input parameter. 

    Read each <Header> and update the Column2 value based on Column1 Value.

    Refer below link for simple WCF-SQL example.

    http://geekswithblogs.net/bosuch/archive/2010/10/19/biztalk---simple-wcf-tutorial-for-using-a-stored-procedure.aspx

    Thanks,

    Prakash

    • Marked as answer by Angie Xu Monday, January 19, 2015 2:36 AM
    Sunday, January 11, 2015 5:35 PM
  • Hi Anand ,

    Its easy to have a insert statement executed in SQL with stored procedure created or directly creating insert schema for your table here .

    What you need here is to map your request schema from excel to stored procedure or table insert schema and then use the auto created binding to insert data into SQL .

    Steef jan has written a great post on his blog in below link

    WCF-SQL Adapter Table Operations in BizTalk

    I have also written a blog post on TechNet Wiki which you can refer

    BizTalk 2013 - CRUD Operation With WCF SQL Adapter and Correlation

    There are also great articles over MSDN which can simply how you can use WCF framework to  Populate data to SQL Server.The SQL adapter discovers a set of basic Insert, Select, Update, and Delete operations on SQL Server database tables and views.

    Performing an Insert Operation on a Table Using the WCF Channel Model

    Note : On my personal view also try to consider data size in your Exce( number of message in rows for insert ) . If size of data is large I see can that you need to pay some consideration towards bizTalk performance as well

      Thanks
    Abhishek

    • Marked as answer by Angie Xu Monday, January 19, 2015 2:36 AM
    Sunday, January 11, 2015 5:46 PM
  • Hi Prakash,

    Thanks for all your suggestions which helped me to understand the things better. But, somehow i couldn't complete it by passing xml as input parameter to SP.

    But, the below thing worked out for me (The same thing which i posetd earlier, but it was working for only 1st record).

    1. I have created the custom pipeline component which converts the input to the XML format.

    2. I have created the Stored Procedure and created output schema using the WCF-SQL Adapter.

    3. Then, mapped the input schema and output schema with direct mapping on "column-1" (which is a parameter for the SP)

    4. Then, I configured receive and send ports and placed the excel in the input location.

    The thing I was missing out was, while configuring the Send Port in the Console, i was referring to "TypedProcedured/dbo/SP_Name" in the SOAP Action Header. Now, I changed it to "CompositeOperation" and it worked for me.

    Thansk all for your inputs.

    Regards,

    Anand


    Anand

    • Marked as answer by Anand M J Wednesday, January 28, 2015 3:49 PM
    Wednesday, January 28, 2015 3:49 PM

All replies

  • If you are just making a simple insert into a single table, there is no need for an SP.

    - Use the WCF-SQL Adapter (the SQL Adapter is deprecated)

    - Create an Insert Schema through Visual Studio

    - Map from your source Schema to the created "SQL Insert Schema"

    - Apply the Map on the Send Port

    - Configure the WCF-SQL Send Adapter

    Morten la Cour

    Saturday, January 10, 2015 7:39 AM
  • Yes. Write a stored procedure which takes xml as input parameter. 

    Read each <Header> and update the Column2 value based on Column1 Value.

    Refer below link for simple WCF-SQL example.

    http://geekswithblogs.net/bosuch/archive/2010/10/19/biztalk---simple-wcf-tutorial-for-using-a-stored-procedure.aspx

    Thanks,

    Prakash

    • Marked as answer by Angie Xu Monday, January 19, 2015 2:36 AM
    Sunday, January 11, 2015 5:35 PM
  • Hi Anand ,

    Its easy to have a insert statement executed in SQL with stored procedure created or directly creating insert schema for your table here .

    What you need here is to map your request schema from excel to stored procedure or table insert schema and then use the auto created binding to insert data into SQL .

    Steef jan has written a great post on his blog in below link

    WCF-SQL Adapter Table Operations in BizTalk

    I have also written a blog post on TechNet Wiki which you can refer

    BizTalk 2013 - CRUD Operation With WCF SQL Adapter and Correlation

    There are also great articles over MSDN which can simply how you can use WCF framework to  Populate data to SQL Server.The SQL adapter discovers a set of basic Insert, Select, Update, and Delete operations on SQL Server database tables and views.

    Performing an Insert Operation on a Table Using the WCF Channel Model

    Note : On my personal view also try to consider data size in your Exce( number of message in rows for insert ) . If size of data is large I see can that you need to pay some consideration towards bizTalk performance as well

      Thanks
    Abhishek

    • Marked as answer by Angie Xu Monday, January 19, 2015 2:36 AM
    Sunday, January 11, 2015 5:46 PM
  • Hi,

    Based on all the valuable suggestions, I have followed the below steps for updating the SQL table from the input excel.

    1. I have created the custom pipeline component which converts the input to the XML format.

    2. I have created the Stored Procedure and created output schema using the WCF-SQL Adapter.

    3. Then, mapped the input schema and output schema with direct mapping on "column-1" (which is a parameter for the SP)

    4. Then, I configured receive and send ports and placed the excel in the input location.

    5. Now, I am able to update the SQL table for only 1st record in the excel. (As I did not add loop in the mapping)

    The WCF-SQL adapter has created the output schema nodes (For ex: Test and TestResponse). Now, if i try to add a looping node(LoopRecords) with maxOccurs='unbounded' for 'Test' and then perform mapping, I am getting the below exception.

    The adapter failed to transmit message going to send port "SendPort1" with URL "mssql://serverName//dbInstanceName?". It will be retransmitted after the retry interval specified for this Send Port. Details:"Microsoft.ServiceModel.Channels.Common.XmlReaderParsingException: The start element with name "LoopRecords" and namespace "http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo" was unexpected. Please ensure that your input XML conforms to the schema for the operation.

    Any help is deeply appreciated.

    Thanks in Advance,


    Anand

    Wednesday, January 21, 2015 11:38 AM
  • Hello Anand,

    I feel you are map is wrong. can you share the map?

    Thanks,

    Prakash


    Prakash

    Wednesday, January 21, 2015 5:22 PM
  • Hi Prakash,

    The map is as below. PedimentoNumber is a SP input parameter.

    I tried the below things:

    1. Direct map between Header and CheckUpdatePedimento

    2. Added a child record "LoopRecords" with maxOccurs as 'unbounded' for output schema, so that the field PedimentoNumber comes below the Looping node.

    a. After this, tried direct mapping between Header to LoopRecords.

    b. Also, tried mapping between Header and LoopRecords using the Looping functoid.


    But, nothing seems to be working as expected. Please let me know what changes i need to make in output schema for mapping to work correctly.

    Anand



    • Edited by Anand M J Thursday, January 22, 2015 5:51 AM
    Thursday, January 22, 2015 5:08 AM
  • Hi Anand,

    You can not modify output schema until you change your stored procedure. I feel your current stored procedure is expecting single PedimentoNumber not multiple no's.

    So, try below options.

    1. Debatch your source schema or extract single header from source schema and then do map as above then call your stored procedure.

    2. The above option gives performance issue because every time you have to hit db for each debatched message.

    to avoid this better way is modify your stored procedure.

    create stored procedure which takes xml as a input parameter. (here you can send entire source message to sp)

    parse each header and update whatever you want.

    in a BizTalk Map use masscopy functoid to copy entire source message and map to input parameter of output schema.

    Thanks,

    Prakash 


    Prakash

    Thursday, January 22, 2015 5:52 PM
  • Hi Prakash,

    Thanks for your suggestions.

    We midified our SP to take XML as input parameter,  and mapped the input schema to output using the masscopy functoid as below.

    Also, in my custom receive pipeline should i use XMLDisassembler in Disassembler stage or what? (As we need to pass the complete XML)

    Even after making these changes, I am getting the below error.

    The adapter failed to transmit message going to send port "SendPort1" with URL "mssql://ServerName//DBInstanceName?". It will be retransmitted after the retry interval specified for this Send Port. Details:"Microsoft.ServiceModel.Channels.Common.XmlReaderParsingException: The start element with name "Expenses_Account" and namespace "http://CostAndStrategyIntegration.Expenses_Account" was unexpected. Please ensure that your input XML conforms to the schema for the operation.

    I believe there is something wrong in the schema which I am using. So, I am pasting the entire schema of Input and Output. Please help me If I am wrong anywhere.

    Input Schema: (Generated using the sample XML output from custom pipeline component using the link - http://www.luxonsoftware.com/converter/xmltoxsd)

    <?xml version="1.0" encoding="utf-16" ?> 
    - <xs:schema xmlns:b="http://schemas.microsoft.com/BizTalk/2003" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:mstns="http://CostAndStrategyIntegration.Expenses_Account" attributeFormDefault="qualified" elementFormDefault="qualified" targetNamespace="http://CostAndStrategyIntegration.Expenses_Account" id="Expenses_Account" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    - <xs:element msdata:IsDataSet="true" msdata:UseCurrentLocale="true" msdata:Prefix="ns0" name="Expenses_Account">
    - <xs:complexType>
    - <xs:choice minOccurs="0" maxOccurs="unbounded">
    - <xs:element minOccurs="0" maxOccurs="unbounded" form="unqualified" name="Header">
    - <xs:complexType>
    - <xs:sequence>
      <xs:element minOccurs="0" form="unqualified" name="Pedimento" type="xs:string" /> 
      <xs:element minOccurs="0" form="unqualified" name="CuentaDeGastos" type="xs:string" /> 
      </xs:sequence>
      </xs:complexType>
      </xs:element>
      </xs:choice>
      </xs:complexType>
      </xs:element>
      </xs:schema>

    Output Schema: (Generated using WCF-SQL Adapter)

     <?xml version="1.0" encoding="utf-16" ?> 
    - <schema xmlns:b="http://schemas.microsoft.com/BizTalk/2003" elementFormDefault="qualified" targetNamespace="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo" version="1.0" xmlns="http://www.w3.org/2001/XMLSchema">
    - <annotation>
    - <appinfo>
      <fileNameHint xmlns="http://schemas.microsoft.com/servicemodel/adapters/metadata/xsd">TypedProcedure.dbo</fileNameHint> 
      </appinfo>
      </annotation>
    - <element name="CheckUpdatePedimento">
    - <annotation>
    - <documentation>
      <doc:action xmlns:doc="http://schemas.microsoft.com/servicemodel/adapters/metadata/documentation">TypedProcedure/dbo/CheckUpdatePedimento</doc:action> 
      </documentation>
      </annotation>
    - <complexType>
    - <sequence>
      <element minOccurs="0" maxOccurs="1" name="XML" nillable="true" type="string" /> 
      </sequence>
      </complexType>
      </element>
    - <element name="CheckUpdatePedimentoResponse">
    - <annotation>
    - <documentation>
      <doc:action xmlns:doc="http://schemas.microsoft.com/servicemodel/adapters/metadata/documentation">TypedProcedure/dbo/CheckUpdatePedimento/response</doc:action> 
      </documentation>
      </annotation>
    - <complexType>
    - <sequence>
      <element minOccurs="1" maxOccurs="1" name="ReturnValue" type="int" /> 
      </sequence>
      </complexType>
      </element>
      </schema>

    Please help me on how to proceed as I am badly struck in this issue.

    Thanks in Advance,



    Anand

    Friday, January 23, 2015 11:54 AM
  • Hi Anand,

    Sorry for late reply..!

    No need to use  XMLDisassembler at Disassembler stage because you are not debatching anything.

    and you are not sending map output (CheckUpdatePedimento) to SQL server instead of this message you are sending input message directly to SQL server and your stored procedure is not expecting this.

    The adapter failed to transmit message going to send port "SendPort1" with URL "mssql://ServerName//DBInstanceName?". It will be retransmitted after the retry interval specified for this Send Port. Details:"Microsoft.ServiceModel.Channels.Common.XmlReaderParsingException: The start element with name "Expenses_Account" and namespace "http://CostAndStrategyIntegration.Expenses_Account" was unexpected. Please ensure that your input XML conforms to the schema for the operation.

    So the message that you are sending to sql server itself wrong.

    Thanks,

    Prakash


    Prakash

    Monday, January 26, 2015 9:23 AM
  • Hi Prakash,

    Thanks for your response.

    Fine, I will not be using and Disassembling component.

    But, can you please help me on mapping the input message directly to output (What changes i should do to Mapping in the screenshot attached)?

    Regards,

    Anand


    Anand

    Tuesday, January 27, 2015 7:05 AM
  • Hi Anand,

    Your map is correct. no change required. 

    As i mentioned earlier, the problem is you are not sending correct message to sql server.

    Thanks,

    Prakash


    Prakash

    Tuesday, January 27, 2015 5:51 PM
  • Hi Prakash,

    Thanks for all your suggestions which helped me to understand the things better. But, somehow i couldn't complete it by passing xml as input parameter to SP.

    But, the below thing worked out for me (The same thing which i posetd earlier, but it was working for only 1st record).

    1. I have created the custom pipeline component which converts the input to the XML format.

    2. I have created the Stored Procedure and created output schema using the WCF-SQL Adapter.

    3. Then, mapped the input schema and output schema with direct mapping on "column-1" (which is a parameter for the SP)

    4. Then, I configured receive and send ports and placed the excel in the input location.

    The thing I was missing out was, while configuring the Send Port in the Console, i was referring to "TypedProcedured/dbo/SP_Name" in the SOAP Action Header. Now, I changed it to "CompositeOperation" and it worked for me.

    Thansk all for your inputs.

    Regards,

    Anand


    Anand

    • Marked as answer by Anand M J Wednesday, January 28, 2015 3:49 PM
    Wednesday, January 28, 2015 3:49 PM