| Author |
Topic |
|
SMerrill
Posting Yak Master
206 Posts |
 Posted - 2003-01-27 : 16:41:36
|
| Environment: SQL Server 7 back-end, Windows XP + MS Access 2002 clients, SQL Data is attached as a "Project"Problem: As the developer, I would like to know which option is the best choice for transferring a text file from the client to the server.I wrote some VBA code in Access 2002 to use an AWK script to process some data. After AWK, it is on the client machine in the temp directory as a large (9,700KB) comma-delimited text file. I wish to write a distributable Access program that painlessly imports this file into SQL Server 7.Access' import mechanism is snoringly slow. I realize that DTS or BCP is very fast, and I wish to capitalize on this speed. I also wish to capture the errors and display them to the user. Which option is best?1. Write SQL-DMO code in VBA to call DTS (but SQL-DMO DLLs must be installed on the client)2. Write a Sproc in SQL that calls DTS, and call the Sproc from VBA (then how do I detect and handle errors)3. Write BCP code in VBA (must BCP be on the client?)4. Transfer the text file to the server first, then do one of the above options5. Something else that I'm not thinking ofThere are so many choices! What is the least effort for speedy code? I purchased the Office XP developers edition, so I can compile a package with a SETUP.EXE that includes more than the standard pieces.Thanks for your help,--SMerrillSeattle, WA |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-01-27 : 20:40:02
|
| Fastest and probably simplest will be bcp.You don't have to install bcp on the client. Can call an SP which does a bulk insert from the server but it will need a share to access the file from. Fastset would probably be to copy the file t the server then call the SP.This won't give very good error handling though.Can insert into a single row table then the SP can validate the data before merging with production tables.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2003-01-27 : 22:33:32
|
| I agree for speed can't beat bcp.I think I once ran a test of 100,000 and was able to import in 15 sec. Previously this was being processed row by row (ugly) and taking more than an hour. |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2003-01-27 : 23:52:57
|
| I'd suggest you don't use BCP. I used to be a huge BCP fan but I've since strayed away from it. Use BULK INSERT instead. The parameters are almost the same. MS has been upgrading the BULK INSERT statement but bcp is still the same old program from a much earlier version. I attended a session at PASS and the speaker did a great presentation on bulk loading. And what do you know, it's available online (http://sqldev.net/download/conf/SQLMagLiveNov2002/SAA211-ppt.zip). The big downside of BULK INSERT is that it's difficult to dynamically change the file name.I'd suggest doing a BULK INSERT into an import table with no data validation at all. Then run SQL scripts to validate the date and move it into the production tables.===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2003-01-28 : 09:59:37
|
| Actually, I was just working on a way to change the file name. I used an activex script which can read global variables or any datasource and changes the name dynamically.The script code is only 10 lines I will try to post later.Thanks for the update graz. |
|
|
|
SMerrill
Posting Yak Master
206 Posts |
Posted - 2003-01-28 : 13:50:03
|
| Gentlemen: Thanks for the many good responses. I personally don't need to change the filename. I will search the web for some VB-FTP code to push the file up to the server. Based upon your input, I should:1. Transfer the file to the server using VBA.2. Code a Bulk Insert into a stored procedure and call it from VBA. 3. Make the initial insert as simple as possible; no constraints, tolerate NULLs.4. Validate the data and whine at the user to fix the source filesRight?--SMerrillSeattle, WA |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2003-01-28 : 13:58:14
|
| Sounds good,You could do this all in a dts package using the ftp task and the bulk insert task. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-01-28 : 13:59:24
|
| Apart from 5,6,7,8,... same as 4 :).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
SMerrill
Posting Yak Master
206 Posts |
Posted - 2003-01-28 : 18:52:49
|
quote:
Sounds good,You could do this all in a dts package using the ftp task and the bulk insert task.
No I can't. Think about it. I'm using version 7. Even if 7 had the FTP task, the file wouldn't be visible to the task running on the server.However, the tack I am taking now seems to work real well. I ended up calling upon the Internet Transfer Control (msinet.ocx) to do the FTP "PUT" from the client to the server. I then wrote a Sproc to do the bulk insert and the validation. Invalid records are popped into temp tables and Access is able to write reports from them. So it's a slick, fast one-button interface that takes 65 seconds to import 900,000 rows and analyze them.--SMerrillSeattle, WA |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2003-01-28 : 19:38:42
|
Aha, yes you're uploading from client to server.Darn! Must read more carefully Edited by - ValterBorges on 01/28/2003 19:45:16 |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-01-28 : 23:01:57
|
| Problem with doing it all in a package would be that everything would be dependent.You couldn't re-use bits for other imports.If the method of import or source of data changes you have to find some way that fits in with the dts package (and doesn't mess up the other steps rather than just replacing the import routine.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
What is the best choice?