Comma delimited from SQL Field

Home Page › Forums › BizTalk 2004 – BizTalk 2010 › Comma delimited from SQL Field

Viewing 1 reply thread
  • Author
    Posts
    • August 10, 2006 at 4:18 PM #15273
      community-content

        Scenario:
        Reading in a file of comma delimeted values (with further delimeters inside those on some fields) from a flat file drop location. I then store each record individually to a work in progress database (business req vs technical req). So the database will contain two columns, ID and Record with Record storing a string like the following..

        Lisa, Smith, 123 Way Street, Check~Cash~CC, 12.34~56.82~10.09, Yellow, etc

        I now, at another date/time, want to pull that data out of SQL Record column. If I use the SQL Adapter it brings records back and I can see them as a single node element. But how do I do Flat File Like delimiter work on a returned SQL Column without writing it out to a flat file and bringing it back in again? Is there a way to do this in a map or orch?

        Is there a way to setup delimeters and hence parse a SQL column that is essentially a comma delimeted record?

        Thanks..

      • August 10, 2006 at 10:02 PM #15274
        community-content

          You can call a static C# method from Biztalk or create an object from a C# class and calls its methods.

          Here’s some sample code:

          http://www.csharp-station.com/HowTo/StringJoinSplit.aspx
          http://builder.com.com/5100-6371-6030362.html
          http://www.peachpit.com/articles/article.asp?p=31938&seqNum=14&rl=1

          You might also be able to use the RegularExpression Object:
          http://www.codeproject.com/books/0735616485.asp?df=100&forumid=3879&exp=0&select=1240629

          • August 17, 2006 at 2:37 PM #15275
            community-content

              you could try creating a pipeline with a ffdasm and call it from your orchestration.

              • August 17, 2006 at 3:07 PM #15276
                community-content

                  I don’t know if this was the best method.. but I did it as follows.

                  Read in via SQL Adapter.
                  Send via pipeline the content to a flat file schema.
                  Retrieve via pipeline the flat file and parse to new schema.

                  Probably not optimal.. but only way given my skill set to get it completed.

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