can biztalk read data from csv file,tab file,partial data from multiple text files, ssh tunnel and send to sql server DB

Home Page › Forums › BizTalk 2004 – BizTalk 2010 › can biztalk read data from csv file,tab file,partial data from multiple text files, ssh tunnel and send to sql server DB

Viewing 1 reply thread
  • Author
    Posts
    • January 7, 2010 at 1:14 PM #24019
      community-content

        hi  guys,

              I am new to Biztalk server 2009, I worked with BS 2000 to convert X9 format files to XML and map them and update to sql server database.

             but currently I have a bigger situation to handle and any information is gr8tly appreciated.

         

        I have information coming from multiple clients in multiple formats and I need to consolidate the data and update my SQL SERVER DB into their respective tables.

        the formats in which I am getting my data is

        1) Comma seperated file

        2) Tab delimited file

        3) partial files i,e ( multiple text files with data that need to be incorporated into one single file and updated to  a table in SQL server DB

        4) SSH Tunnel (data is received in this process too)

        how can i parse this data using biztalk server and update my sql server database – is it even possible or should i look out for any other better option.

        thank you

         

          

      • January 7, 2010 at 6:15 PM #24024
        community-content

          This is a classic Biztalk pattern, receiving multiple messages of different formats and updating a single back-end system (SQL database).

          1. Create message for updating your SQL database.
          If you use the new WCF SQL adapter you can use tables, rows, stored procedures, you can even pass a table to a stored procedure if you have SQL 2008.

          2. Create a schema for each different input message type
          3. Create a map from each input message type to your SQL message.

          This should be easy for the comma and tab delimited files.
          For the partial files you will need to implement an aggregator sequential convoy to aggregate the separate text files into a single message before mapping into the SQL message. http://abdulrafaysbiztalk.wordpress.com/2009/11/07/enterprise-integration-pattern-part-5-aggregator-sequential-convoy/
          Not really sure what is happening with the SSH tunnel, but  /n software have a SSH adapter http://www.nsoftware.com/products/biztalk/adapters/ssh.aspx  

          • January 8, 2010 at 10:53 AM #24032
            community-content

              THANK YOU very much

               

              this is real great help. I appreciate your time and info.

               

               

              • January 11, 2010 at 2:13 PM #24047
                community-content

                  My data from multiple CSV files is not in ordered format eg

                  I have 3 csv files –

                  File A has 25 fields

                  File B has 30 fields

                  File C has 100 fields

                  and I need to map these files to unified format in biztalk so that data from these 3 files can be imported to sql server database (how can i gdo it

                   

                  thanks

                   

              • January 15, 2010 at 2:20 AM #24079
                community-content

                  What’s the QCF SQL adapter?

            • Author
              Posts
            Viewing 1 reply thread
            • The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.