Ok folks, Here's my situation.
Tomorrow I have to go and interview with the IT guy at a large company and convince him that I can do his project. They download a zip file with 30,000,000 records and about 20 columns each day. They extract the text file it contains, then import it into SQL Server, replacing yesterday's import. They say the import process takes them about 4 hrs and they want it faster.
Of course, like usual, I told them, "sure. I'll be in tomorrow and take care of it."
Then again.....I've never had to deal with tables of more than 50k records or so. At that size, SQL Server doesn't even show a load when I reindex or run horribly inefficient queries. With SQL Server rather than Access, I can spend 90% of my time on the interface and clean up the SQL as necessary. This is going to be QUITE a bit different.
I can't remember the last time I did a DTS package! I vaguely remember something about BULK INSERT, but I'm going to have to do some cramming tonight.
Finally, they want to automate the entire process. So here is my plan and I though maybe you'd be able to offer a few different options if the IT guy in the second interview has questions.
1. make a script that logs into their main FTP server and downloads the file.
2. add a line to unzip the file using pkunzip or, if they have it, the command line options for winzip
3. create a dts package that executes this script
4. followed by a truncate table on the existing table
5. followed by a BULK INSERT
6. schedule it to start at 3am
(remember, I don't know ANYTHING (hardware, CPU, etc) other than what I have written here)
thanks for your suggestions!
Start Free Trial
