csv import to SQL Server triggered by file movement

From: Mark Jackson (mark.jackson_at_ncumbria.nhs.uk)
Date: 02/11/05 

Date: 11 Feb 2005 05:56:03 -0800

Hi,

I'm new to DTS and am trying to work out the best way to approach a
task. I was wondering if anyone could advise me?

The situation is that we have a folder which, every few hours, will
have a number of .csv files deposited into it, which will all have
unique filenames. What I need to do is to transfer the data from each
of these files (one at a time) into a table in a SQL Server database
(the table used will be determined by a part of the file's filename),
then finally move the file to an archive area.

Now, the way I had planned to do this was to use an ActiveX script
task to do a scheduled scan of the folder, then when the system
recognised a file/files had arrived, to temporarily move the files one
at a time to a temp location. Wile in this area I would rename the
file to a standard filename which means I could then apply another
task (data transform) which would transfer data from this recognized
filename into the db.

The problem with this is that I only know how to loop through ALL
files in a folder (For Each fsoFile in fsoFolder.Files etc) whereas
I'd need to pick the FIRST file only in the folder, in order to be
able to import then archive the files one at a time - I don't know if
it's possible to do a sort of ‘For First fsoFile in fsoFolder.Files'
or whatever. Also, I'm not actually sure how to call another task from
within an ActiveX script object - I've not seen any examples on the
net of doing this.

The other alternative would be to try and do the whole thing using an
ActiveX script, which would mean finding a way of transferring data
from a csv file into a SQL Server table using scripting. Does anyone
know if there are any sites that might have examples of what I'm
trying to do, or would anyone be able to advise which of the two
methods I've come up with would be best?

I really appreciate any advice on this! Many thanks.

Relevant Pages

  • RE: csv import to SQL Server triggered by file movement
    ... If you are using MS SQL Server 2000, I have for you easy solution: ... > (the table used will be determined by a part of the file's filename), ... > task which would transfer data from this recognized ... > within an ActiveX script object - I've not seen any examples on the ...
    (microsoft.public.sqlserver.dts)
  • Re: Warning Message to Prevent Overwritting
    ... if the description on the cell "D13" is WO9999. ... 2.- Create a filename WO9999.xls and located under the directory WO9999 ... The mkdir command either creates the folder or fails to create the folder. ...
    (microsoft.public.excel.programming)
  • RE: how to select, rename, and save a file using Access VBA ?
    ... So far no luck with that portion, but the file overwriting ... MsgBox "Filename Exists, Try a different filename." ... and it is saving the associated graphics file into the ... folder if they have the same name. ...
    (microsoft.public.access.modulesdaovba)
  • RE: how to select, rename, and save a file using Access VBA ?
    ... So far no luck with that portion, but the file overwriting ... MsgBox "Filename Exists, Try a different filename." ... and it is saving the associated graphics file into the ... folder on my hard drive. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Recursive Function + File searching to return path
    ... Tier 1 Filename: A.xls ... finding' spreadsheet is going to be in a folder which contains another ... That other folder contains the entire hierarchy. ...
    (microsoft.public.excel.programming)