Hi,

I am using Biztalk 2004 + SQL server 2000 database. I need some
information about database connectivity.
For example, there is large incoming message.The data of which had to
be stored in database table.
My assumption is no database functoid which helps in inserting and
updating the custom database and have to create custom functoid or
stored procedure. Is it currect?

In custom functoid I connect,open,insert and finally close the
database. But everytime I use the functoid this will repeat. Instead of
this I want have a one time database connection, then transformation of
maps and the close the database.
How can I achieve database connectivity at one time instead of
connecting and opening at each functoid operation in the map? Can it be
done in orchestration?
Or is there any other approach for this issue?

Any help is appreciated.

Regards,

[ Post a follow-up to this message ]

While there is a way, you can create a single database connection per map, I
don't believe doing SQL inserts in a map is the best way to do this.
SQL access in maps is more about simple lookups and data transformations,
not data inserts.

The standard mechanism would be to use the SQL Adapter and a send port to
perform this function.
The SQL send port can be used from an orchestration.

Greg


"Sahana" <diyasn@rediffmail.com> wrote in message
news:1140675704.622127.99130@i40g2000cwc.googlegroups.com...
> Hi,
>
> I am using Biztalk 2004 + SQL server 2000 database. I need some
> information about database connectivity.
> For example, there is large incoming message.The data of which had to
> be stored in database table.
> My assumption is no database functoid which helps in inserting and
> updating the custom database and have to create custom functoid or
> stored procedure. Is it currect?
>
> In custom functoid I connect,open,insert and finally close the
> database. But everytime I use the functoid this will repeat. Instead of
> this I want have a one time database connection, then transformation of
> maps and the close the database.
> How can I achieve database connectivity at one time instead of
> connecting and opening at each functoid operation in the map? Can it be
> done in orchestration?
> Or is there any other approach for this issue?
>
> Any help is appreciated.
>
> Regards,
>

[ Post a follow-up to this message ]

Thanx Greg,
But I think I didn't put my problem properly.
The message transport is through file drop.During mapping, based on
certain rules, only few field values from incoming message are
updated/inserted into the custom tables.But out going transport is
again filedrop.

So how SQL adapter help in this cituation?

[ Post a follow-up to this message ]

Sahana,
if you know what the fields are in the target schema, You could try just
doing the mapping in the recieve port or inside the orchestration. Once that
is complete, you could create a parallel branch and put sends in each. Befor
e
sending to SQL you could do another mapping where you extract only the field
s
needed for SQL and send it via an updategram or stored procedure. The other
branch would simply take the target file and drop it into the recieve
location.

HTH
Benjy

"Sahana" wrote:

> Thanx Greg,
> But I think I didn't put my problem properly.
> The message transport is through file drop.During mapping, based on
> certain rules, only few field values from incoming message are
> updated/inserted into the custom tables.But out going transport is
> again filedrop.
>
> So how SQL adapter help in this cituation?
>
>

[ Post a follow-up to this message ]