June 5, 2005 at 5:22 pm
Our developers want to write a web service that loads a large amount of data into a sql 2000 database. The idea is to create a DTS Package with the DTS designer (they like the visual parsing of the fixed field data) and execute this package using "xp_cmdshell..... DTSRun ..." on the sql server box.
Does DTSRun return any status codes (as in "success" or "failure" to load data) that xp_cmdshell can return to the calling routine? (I have looked for returned status codes for both DTSRun and "bcp" but haven't found any.)
TIA,
Bill
June 6, 2005 at 10:09 am
Hi,
have you considered usign Execute process task in DTS ? This has a return code. and inside a batchfile ( you write bcp statement inside) you can return 0(success) or 1(for failure) after checking for error level.
HTH
THNQdigital
June 6, 2005 at 11:47 am
Thanks for the thought! I wasn't thinking along that line at all as the developers are not famliar with DTS coding practices and wanted to use the Visual tools.
Bill
June 7, 2005 at 10:57 am
Using the ImportData method of the Table object in SQLDMO, you can do Bulk Insert to copy rows into tables, and the method returns the number of rows added. Of course, if there is an error, then you will have the standard error handling within your VB program to handle this.
Bulk Insert represents "the fastest method for copying large amounts of data" in SQL Server (quoted from BOL). Too often in these forums are people using DTS instead of this better/faster/(therefore)cheaper method.
Mark
June 7, 2005 at 12:58 pm
Our simple solution to disconnected methods like you are mentioning was simple VBSCRIPT.
Instead of trying to crack the DTS shell, we decided an easy route of being happy to sit outside.
We can call DTS from many ways cmdshell, OSQL, etc....but , from there, its in its own ricebowl (security is good..).
We have machines that control each other by old fashioned file writes using scripting in DTS.
A DTS is called..... a script command runs inside it... a file is written to a folder. Other machines are watching that folder.
The DTS pauses and waits for the other machines to acknowledge the file...
the other machine sees the file, takes action, deletes it, replaces with its own "success" file, DTS sees that, proceeds to next step...
( example of some ways to use external controls...)
of course there are all the usuall error handlers , etc...
Such methods were created to be used this way, not like today where they seem to only be used by cracker kiddies running BOTNETS and polling for zombies!
maybe too old fashioned , or not in context for you, but its real solid for us and completely automated.
Our usage is connecting 2 completley different manufacturing systems, on different networks, using secure VPN.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply