Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
 Posted - 2004-05-24 : 13:15:29
|
| I have noticed that when I call a DTS from a Visual Basic DLL that my server processor hits 100% and stays there. I have tested everything else in my DLL for issues and I am left with the DTS portion.My DTS is trying to import a 65,000 record text file into a SQL database. If I build the DTS in Sql Enterprise Manager and run it, the task takes apporx. 30 sec and never gets above 75% processor usage. When I call it from my DLL it takes longer than 5 minutes at 100% processor usage.I am using an older machine, but it is my test server for that very reason. Any thoughts?Seventhnight |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-24 : 13:53:23
|
| If you are just importing data, why not use bcp.exe instead of DTS? Or how about BULK INSERT? DTS is the slowest of the 3.Tara |
 |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-05-24 : 14:01:47
|
| I was using DTS for a couple of reasons (could change my mind...):I am familiar with DTSThe files I am uploading are not of the same specifications (1 or more of: different column count, different column orders, different delimiters, different column lengths)I am trying to trigger from a dll.I am not familiar with bcp.exe, would I still be able to accomplish my goals?My main question is the difference in processing the same file from each method. When run from Sql EManager the import is fine, from the DLL it is slow. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-05-24 : 14:04:54
|
| How are you calling it from the DLL? Where does the DLL run? Could be network latency if the package is being called from a machine that is not the database server. Do you need to call it from the DLL or can you schedule it on SQL Server using SQL Agent?Yes bcp can do the different file formats.Tara |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-05-24 : 14:20:11
|
| The DLL is being called by the web server. A web application selects the file to import, and allows the user to customize the import for the specific file (setting the delimiters, column lengths and what not...) At this point, the Web Server and DB server are the same server (test environment), but ultimately they will be different servers.I am reading about bcp... but I'd like to figure this out as it is an object that I can retrieve errors and events from. |
|
|
|
|
|
|
|
|
DTS Processing