csv import to SQL Server triggered by file movement -- sql server dts

mark.jackson NO[at]SPAM ncumbria.nhs.uk
2/11/2005 5:56:03 AM

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?

Tomasz Borawski
2/11/2005 6:25:02 AM

Hi,

If you are using MS SQL Server 2000, I have for you easy solution:
- create a DTS Package for one file
- create a global variable which store file name
- use dynamic property task to update source information in you package
- use workflow to setup dependency between transformation task and dynamic
property
- write a VBScript file that executes for each file a DTS Package with
setting a global variable.

Tomasz B.

[quoted text, click to view]

mark.jackson NO[at]SPAM ncumbria.nhs.uk
2/11/2005 7:03:47 AM

Thank you Tomasz, I appreciate your help.

Regards, Mark

Allan Mitchell
2/11/2005 3:48:38 PM

Tomasz

Not sure this way would work from what I read of the poster's
requirements

1. The format of the files by all accounts could be different each
time.
2. The file/s we are looking for will be mixed in with all the previous
files.
3. The filenames are not guessable

You will probably need to look to

1. Load all files you have
2. Store the end time in a DB table
3. At the next run look in that table to find the last date
4. Go through each file in the directory and see if its creation time >
that time

You could then try to build up a cp statement of a BULK INSERT statement
to get the file loaded.

"Tomasz Borawski" <TomaszBorawski@discussions.microsoft.com> wrote in
message news:TomaszBorawski@discussions.microsoft.com:
[quoted text, click to view]

mark.jackson NO[at]SPAM ncumbria.nhs.uk
2/14/2005 5:43:30 AM

Hi Allan,

Thanks for your assistance too.

In actual fact, once a file has had its data transferred into the
staging database, it will be moved into an archive area, and so in fact
there will only ever be new files each time (which means I should just
be able to use a 'For each file in folder...' style loop). In addition,
although the files will indeed have unique names, these will
nevertheless contain identifying text which I can use to determine
which table the data needs to go into. So, using the method of having
one package to handle the files, and another to transfer the data (with
file name and destination table being set as global variables from the
first package) I should be able to do it this way.

The only snag is that each table (and therefore each 'type' of csv
file) has a different number of columns, and so I will need to find
some way of controlling the transformation (column map) in the data
transfer task by global variable too.

Many thanks

Mark.

mark.jackson NO[at]SPAM ncumbria.nhs.uk
2/14/2005 8:48:19 AM

Hi Allan

Many thanks for your help. Just to clarify the situation, the files
will actually be permanently moved to an archive section after
processing, so in fact there will only be new files present in the
directory (meaning I can use the 'For each file in folder...' method
each time I check for files. Also, although you are correct that the
filename will be different each time, part of it will contain an
identifier informing me which table the data needs to go into.

So basically, it looks as though I can use the method of two packages
- one to handle the files...the other to facilitate data transfer from
a file into a SQL table with global variables controlling the filename
and destination table.

The only snag now is that each potential destination table (and
therefore each 'type' of csv file) has a different number of
fields/columns, so I need to find some way of setting the
transformation/column mapping using a global variable too (to set it
to one of a pre-defined number of column maps)...will look into this
though.

mark.jackson NO[at]SPAM ncumbria.nhs.uk
2/15/2005 12:55:37 AM

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server dts

sql server dts : csv import to SQL Server triggered by file movement