LearnItFirst User Forum

Welcome Guest

Search | New Posts | Members | Log In | Register

SQL Server Forum - LearnItFirst.com » Transact-SQL Programming » Stored Procedures, Triggers, & Functions » Best Method To Load Files From Folder To SQL table

Best Method To Load Files From Folder To SQL table

Options

Previous Topic · Next Topic

Neuralgroove

Posted: Tuesday, March 25, 2008 8:19:34 AM

Rank: Newbie

Joined: 3/25/2008
Posts: 1
Points: 3
Where do you live?: calgary

I'm super new to SQL Server, I know databases but have always just used Access, VBA and ADO to get things done, mostly because our IS department won't give me any SS space to develop and learn with. That has changed and now I've been tasked with developing on a SQL Server box and I'm clueless.

I know this is probably very simple but I've spent hours on the web and can't seem to find my starting place.

Basically I have a folder that stores Accounting files, a new one every day, with a yyyymmddhhmmss time-stamp naming convention. All I have to do is pull the data from the new file in every day. Catch is, I want to start by pulling all of the existing files in right now, and then have the package scan the folder every day and just pick up the new ones. What is the best way to do this?

I set up a DTS package that loads the data from the csv (text files) into the table that I created in the database. But the filename in the DTS package is static, so I'm thinking I need to be able to pass the DTS a file name. I was thinking that I could use the FileSystemObject to get a directory list, and then have another table that stores the filenames of files that have already been loaded, thus, if the name isn't in the table, send that filename to the DTS and load it. Do you do this with a stored procedure? where would the loop be located that gets the directory list and calls the DTS?

Any help would be most appreciated!

Scott Whigham

Posted: Wednesday, March 26, 2008 3:54:01 PM

Rank: Super Mod

Joined: 3/20/2006
Posts: 476
Points: 1,053
Where do you live?: Dallas, TX

Hi there -

DTS, eh? Crap. If it was SSIS, this would be easier.

With DTS, you're going to have to write some ActiveX scripting code using the FileSystemObject - that's the name in VBScript - to loop through every file in the folder. Then you've got to really learn DTS inside out - you have to learn how to programmatically call another task in DTS (which ain't easy).

Is there any way you can do this in SSIS? If so, there might be no code written at all! You could use a Foreach container to load all the files and then a FileWatcher to watch for a file.

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.