SQL Import

Baz · 12 Mar 2007 at 13:08

Easy question, not so easy answer..

I have a lot of files I wish to import into SQL Server on a regular basis, like every week. The files are CSV files, each with a different filename.

How can I create a script that will import the files, transfer the filename into a column in the table. I have already looked at the bulk import in BOL, but it's not easy to understand..

Any help is appreciated

~J~ · 12 Mar 2007 at 13:12

Piece of cake, providing you follow some naming pattern

Create a DTS package that looks in a folder where the CSV files are to be kept.

Create a table that has a value that can be incremented/changed on a sensible basis. E.G. Filename00001, 00001, File_A, etc.

In the DTS package, create a new VBScript node, and within there code it to look in the directory for a filename matching what's in the table, when it finds it, do the necessary 'stuff', then increment the value in the table by one unit.

Baz · 12 Mar 2007 at 16:05

OK, cheers, found a few webpages when searching for DTS.

Any problems, and I'll be back

Baz · 13 Mar 2007 at 14:45

Ok, trawled through lots of websites, and now I am offically confused :confused:

I have tried to create a DTS package, but I cannot see how to link them together, or how to tell it to go to another file, which it hasn't previously imported.. or take the filename, which unfortunately isn't predictable...

The next version of SQL wants Voice activation for dummies built it... 'Hey SQL, import them there files into that table.!" much easier

Mr^B · 13 Mar 2007 at 21:03

You can use ActiveX routines as part of the overall DTS package - I use one to search for filenames matching a certain date/time and filename pattern, import them, and then delete the files once they've been successfully imported.

It's just a case of adding new steps using the DTS package designer GUI - for which there's plenty of online tutorials, such as:

http://www.devguru.com/features/tutorials/DTS/DTS2.asp

Baz · 14 Mar 2007 at 08:14

Cheers, I had found some tutorials online, but they were all for importing just one file.... which would take some time with the amount I have