March 6, 2008 at 9:04 am
Hi,
I am new to SQL Server and I have one scenario, i would like if anyone can help me.
I have to automatically upload the data from .csv files (resides on an ftp server).
a) ftp server
b) user name / password
c) server may have 20 files, but i need to only upload the files starting with "Q1" (might be only 6 files). Also, file names would be changed on each quarter, what is best way to handle this issue. Do I need to store the file names in the database or any other way?
d) I need to re-load all these files daily, becuase data in the .csv files would be updated. So, I would need to delete the existing data from SQL server and upload again.
e) SQL Server 2000
It would be great, if someone can give me steps to follow.
I appreciate your time and cooperation.
March 6, 2008 at 2:44 pm
Have you ever considered to use a scheduled DTS job?
March 6, 2008 at 4:15 pm
No offense... but this isn't really something that someone "new to SQL Server" should be asked to handle. You need to hire a good DBA to help you do this...
--Jeff Moden
Change is inevitable... Change for the better is not.
March 6, 2008 at 5:02 pm
Uh, I'm for hire. 🙂
Both Jeff and SQL Oracle are correct. This to me sounds like a good place for some VB script in DTS. This is definately not a task for a beginner.
1. Create a FTP script that pulls down only the files you want to load into SQL Server from the FTP site to a local working folder.
2. Drop/add tables.
3. Create some vb script to loop through the folder and bcp the files in one at a time. I personally would create some way to audit this step even if it is as simple as counting the rows in the file and comparing that to the # of sucessful inserts.
4. Create indexes on tables.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply