BizTalk Server General - Split the flat file and fill SQL tables

Author

Split the flat file and fill SQL tables

maverick

2004-10-18, 2:26 pm

I have a flat file that is positional. There are certain characters (i.e|, or #) etc at various positions. Now I need to split this file at the position of these specific characters and on basis of the "characters" present, I need to assign the whole record (till that character)to a "specific" table in SQL server.

What is the end-to-end solution for this?

captor

2004-10-18, 2:46 pm

Maverick,

1. Create a schema for delimited flat file (Follow steps from other post).
2. Search web for SQL Adapter Sample or get it from

http://home.comcast.net/~sdwoodgate...pterSamples.doc

3. Use a map to do the conversion depending on your requirements.

The document I mentioned above is quite helpful if you are new to Biztalk. It has a lot of examples for updating SQL Tables and also reading from SQL Tables

maverick

2004-10-18, 5:50 pm

quote:
Originally posted by captor
Maverick,

1. Create a schema for delimited flat file (Follow steps from other post).
2. Search web for SQL Adapter Sample or get it from

http://home.comcast.net/~sdwoodgate...pterSamples.doc

3. Use a map to do the conversion depending on your requirements.

The document I mentioned above is quite helpful if you are new to Biztalk. It has a lot of examples for updating SQL Tables and also reading from SQL Tables

Thanks for the post. But how will I create a solution where I can transfer records on the basis of the position of certain characters present in the input file. i.e if Character "A" is present then send the data that record in the file to Table A and if Character "B" is present then send that record to Table B.

How will I incorporate this solution in the mapper.?

Ruslan Yakushev [MSFT]

2004-10-22, 5:49 pm

Very high level description of the steps you need to do to achieve this:

1. Define a flat file schema that describes the format of your positional
file.
2. Create a SQL send port to your database table in the SQL server. You can
do this by using the "Add generated items" menu in solution explorer on
VS.NET (right-click on project, select "Add" -> "Add generated item" ->
"Add adapter" -> SQL).
3. Once you've done #2 there will be an XML schema added to your project.
This schema corresponds to the structure of the database table where you
want to write data to.
4. Now you need to create a map that transforms the flat file schema to the
schema generated by SQL adapter.
5. After that you need to create a receive pipeline with the flat file
disassembler in it and configure the disassembler to use the flat file
schema you defined in step #1.
6. Lastly you need to hook this all up together by creating recieve port,
receive location with your receive pipeline, and connecting the SQL send
port to that receive port by using filter expression on send port. Also you
need to place the map you created in the step #4 either in receive port or
send port.

--------------------
>From: maverick <maverick.1ecg98@mail.webservertalk.com>
>Subject: Split the flat file and fill SQL tables
>Date: Mon, 18 Oct 2004 14:26:36 -0500
>Message-ID: <maverick.1ecg98@mail.webservertalk.com>
>Organization: Web Servers forum
>User-Agent: www.webservertalk.com news gateway
>X-Newsreader: www.webservertalk.com news gateway
>X-Originating-IP: 167.211.190.2
>Newsgroups: microsoft.public.biztalk.general
>NNTP-Posting-Host: ev1s-66-98-192-98.ev1servers.net 66.98.192.98
>Lines: 1
>Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12
.phx.gbl
>Xref: cpmsftngxa06.phx.gbl microsoft.public.biztalk.general:20628
>X-Tomcat-NG: microsoft.public.biztalk.general
>
>
I have a flat file that is positional. There are certain characters
(i.e|, or #) etc at various positions. Now I need to split this file at
the position of these specific characters and on basis of the
"characters" present, I need to assign the whole record (till that
character)to a "specific" table in SQL server.
>
>What is the end-to-end solution for this?

--
maverick
------------------------------------------------------------------------
Posted via http://www.webservertalk.com
------------------------------------------------------------------------
View this thread: http://www.webservertalk.com/message437784.html

>

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

Note: For the benefit of the community-at-large, all responses to this
message are best directed to the newsgroup/thread from which they
originated.