Hi,

Which is a preferred method of integration with a limited capability business partner having SQL Server and VPN?
Options:

1. Direct JDBC Insert over VPN from the DMZ to Business partners SQL Server
2. Sending the data in a flat file

Please let me know as early as possible. Thanks for the help in advance.

Thanks & Regards,
Srivats

Option 2.

Directly updating a partner’s database has many risks. There are software compatibility issues, and exposure to service interruptions due to changes on your end or theirs. If your limited-capability partner accidently forgets some security constraints, you don’t want to be responsible for possibly screwing up some of their important data.

It’s been a while since I looked into it, but JDBC network traffic used to be “chatty” in small bursts. This caused performance issues over slow links or over long distances with significant network latency.

I think that using a flat file will make your life a lot easier in the long run. webMethods provides some pretty good tools for making this relatively painless.

I concur. Sending a file over the VPN connection is most likely the way to go. Writing directly to the DB would be advisable only if the SQL Server tables you would be writing to are specifically setup for this sort of import operation (e.g. interface tables, not application tables).

Yes, flat file will be best suitable for this kind of operation. The SQL Server has DTS which can load flat file data from any folder to the database so there won’t be much coding.

FTP the flat file and SQL DTS will pick that up from that location and load data. schedule the DTS service in accordance with your requirement.

Loading to a interface table might be possible but again it will be a JDBC connection and operation might be slow. Again loss of connection etc will be there.

Hi,

Thank you very much for the replies. We were asked to go temporarily live with JDBC (as it is a staging table area in the DMZ) so that partner can prepare their DTS package and in future we would be converting that to Flatfile.

Thanks & Regards,
Srivats

If the partner can build simple ASP pages, just have them create one that receives the post of an XML file and then updates or inserts the data to the appropriate SQL Server tables. It requires some development on their end, but would be more reliable than FTP / DTS if done properly.

Mark (from Jakarta)