February 4, 2002 at 2:32 pm
Is there sample code or pointers to using DTS programming in VB to import a text file? I have VB 6.0.8169 and SQL Server 7.0
February 4, 2002 at 3:01 pm
you can try here:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtsprog/dtspapps_0rab.asp
but the best way is to save your DTS package as a VB file. In v7.0, I think this was an add-in. It's native in 2000.
Steve Jones
February 4, 2002 at 3:08 pm
To answer your question, there's sample code on executing a DTS Package from VB in the articles under Programming -> Visual Basic. You'll want to look to MSDN on how to handle errors returned from the package.
If I may make a suggestion, you'd be better off using bcp to bring in the data file and a stored procedure to handle any transformations. A dts package called from a vb application will drag the file through the client box for processing on it's way to the server. If you call xp_cmdshell, bcp will take the file from it's location directly into SQL. It's faster and more efficient.
Another alternative is to set up a job on the server. You can then use DMO in VB to execute the job that runs the DTS package, however; this brings in yet another layer and more code to maintain.
Have fun,
John
February 4, 2002 at 5:51 pm
John, I have to question your comment that running DTS via VB runs all the data through the client. Not impossible mind you, just haven't ever tested to see yes or no. Did you actually test this and if so, how? Inquiring minds...
Andy
February 4, 2002 at 9:14 pm
Andy,
I've created and executed DTS packages both in an exe on the client box and in a middle tier dll in MTS. In both cases, the client machine's processor utilization spiked while the DTS package was running.
The same thing appears to happen when running a DTS package from EM. If you schedule a job to execute the DTS package it runs faster than if you execute the package from your workstation.
I don't have documentation or scientific test results to back this up, just an observation and MHO. 🙂
John
February 5, 2002 at 7:33 am
Thanks a lot for your prompt answers, people. I have beginner's question. What is bcp? DMO? Can I read up on some of these?
quote:
Andy,I've created and executed DTS packages both in an exe on the client box and in a middle tier dll in MTS. In both cases, the client machine's processor utilization spiked while the DTS package was running.
The same thing appears to happen when running a DTS package from EM. If you schedule a job to execute the DTS package it runs faster than if you execute the package from your workstation.
I don't have documentation or scientific test results to back this up, just an observation and MHO. 🙂
John
February 5, 2002 at 9:57 am
BCP stands for Bulk Copy Program. It's a command line utility that comes with SQL Server designed to import and export data between SQL Server and text files. Look up "bcp utility" in SQL Books Online (SQL Server's help file) and read the overview.
You'll also need xp_cmdshell. This is a special type of stored procedure that allows you to execute command line commands as if you were sitting at the console.
Good luck,
John
February 5, 2002 at 10:53 am
DMO (or SQL-DMO) is an object framework that gives a programmer access to SQL Server and its objects (and to some extent the data).
John - interesting. Brian K is out of touch for a few days, will have to see if he can add more to this point. I heard at PASS that you can problems with threading in DTS packages when run from VB (because of its threading model) but didnt get a chance to hear the session that covered it in detail. Perhaps another reason to run via a job. The biggest reason why I pull packages into VB is that I have a lot more control over what happens. But you could easily just modify the package in a VB exe, then call the job to run it.
Andy
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply