|
Home > Archive > BizTalk Server General > July 2004 > Updategrams?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Guy Barrette 2004-07-26, 5:53 pm |
| 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
| |
| Jeff Lynch 2004-07-26, 5:53 pm |
| 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
>
>
| |
| Guy Barrette 2004-07-26, 5:53 pm |
| 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
>
>
| |
| Jeff Lynch 2004-07-26, 5:53 pm |
| "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]
>
>
| |
| Yossi Dahan 2004-07-27, 2:52 am |
| 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
>
>
|
|
|
|
|