From a BizTalk newbie.....



I have a flat file that I need to import into SQL Server.  I created the
schema and now I'm puzzled by the technique I need to use.  The file won't
have the record's state meaning that I'll have records that will need to be
inserted, deleted and updated (all from the same file).  Do I use an
updategram?  From what I read, Biztalk updategrams do only one of these:
insert, delete and update.  Am I wrong?



Thanks

Guy Barrette

[ Post a follow-up to this message ]

It sounds like you need to "refresh" your table with each upload. This can
be accomplished by using a SQL stored procedure to truncate the table and
then insert all the new and changed data. The SQL adapter can be used to
call this stored procedure from BizTalk.

--
Jeff Lynch
"A BizTalk Enthusiast"
http://dotnetjunkies.com/WebLog/jlynch/



"Guy Barrette" <someone@nurun.com> wrote in message
news:eQUPYy0cEHA.712@TK2MSFTNGP09.phx.gbl...
> From a BizTalk newbie.....
>
>
>
> I have a flat file that I need to import into SQL Server.  I created the
> schema and now I'm puzzled by the technique I need to use.  The file won't
> have the record's state meaning that I'll have records that will need to
be
> inserted, deleted and updated (all from the same file).  Do I use an
> updategram?  From what I read, Biztalk updategrams do only one of these:
> insert, delete and update.  Am I wrong?
>
>
>
> Thanks
>
> Guy Barrette
>
>

[ Post a follow-up to this message ]

Hi Jeff

Good point, however I forgot to mention that the flat file contains
incremental changes (new records, records to delete and records to update),
not the whole enchilada  :-(


Thanks
Guy Barrette


"Jeff Lynch" <jeff.lynch@houston-lynch.com> wrote in message
news:OJ8zj90cEHA.3728@TK2MSFTNGP09.phx.gbl...
> It sounds like you need to "refresh" your table with each upload. This can
> be accomplished by using a SQL stored procedure to truncate the table and
> then insert all the new and changed data. The SQL adapter can be used to
> call this stored procedure from BizTalk.
>
> --
> Jeff Lynch
> "A BizTalk Enthusiast"
> http://dotnetjunkies.com/WebLog/jlynch/
>
>
>
> "Guy Barrette" <someone@nurun.com> wrote in message
> news:eQUPYy0cEHA.712@TK2MSFTNGP09.phx.gbl... 
won't[vbcol=seagreen] 
> be 
>
>

[ Post a follow-up to this message ]

"Whether an updategram deletes, inserts, or updates a record instance
depends on the contents of the <before> and <after> blocks. If a record
instance appears only in the <before> block with no corresponding instance
in the <after> block, the updategram performs a delete operation. If a
record instance appears only in the <after> block with no corresponding
instance in the <before> block, it is an insert operation. If a record
instance appears in the <before> block and has a corresponding instance in
the <after> block, it is an update operation. In this case, the values
specified in the <after> block update the record instance. "

You can configure your map from the inbound flat-file format to the outbound
XML format used by the SQL Adapter to correctly "map" each of these assuming
you know which is which in the flat-file. I usually "normalize" any inbound
flat-files to a known "internal" XML standard (using a map on the receive
port) and then map from the internal XML format to the XML format generated
by the SQL Adapter Wizard (using a map on the send port). Map new records to
the <after> block, changed records to both and records to be deleted to the
<before> block.


--
Jeff Lynch
"A BizTalk Enthusiast"
http://dotnetjunkies.com/WebLog/jlynch/



"Guy Barrette" <someone@nurun.com> wrote in message
news:uTQOIK1cEHA.3532@TK2MSFTNGP11.phx.gbl...
> Hi Jeff
>
> Good point, however I forgot to mention that the flat file contains
> incremental changes (new records, records to delete and records to
update),
> not the whole enchilada  :-(
>
>
> Thanks
> Guy Barrette
>
>
> "Jeff Lynch" <jeff.lynch@houston-lynch.com> wrote in message
> news:OJ8zj90cEHA.3728@TK2MSFTNGP09.phx.gbl... 
can[vbcol=seagreen] 
and[vbcol=seagreen] 
the[vbcol=seagreen] 
> won't 
to[vbcol=seagreen] 
these:[vbcol=seagreen] 
>
>

[ Post a follow-up to this message ]

The last time I had to do something like this I have use u-g to insert the
newly arrived data to a temp table and then executed a stored procedure to
compare the two and perform any actions needed (insert, update or delete)


"Guy Barrette" <someone@nurun.com> wrote in message
news:eQUPYy0cEHA.712@TK2MSFTNGP09.phx.gbl...
> From a BizTalk newbie.....
>
>
>
> I have a flat file that I need to import into SQL Server.  I created the
> schema and now I'm puzzled by the technique I need to use.  The file won't
> have the record's state meaning that I'll have records that will need to
be
> inserted, deleted and updated (all from the same file).  Do I use an
> updategram?  From what I read, Biztalk updategrams do only one of these:
> insert, delete and update.  Am I wrong?
>
>
>
> Thanks
>
> Guy Barrette
>
>

[ Post a follow-up to this message ]