Automating a DTS Package

Question

Automating a DTS Package

Apollo74x

Ten Centuries

Points: 1250
More actions
November 2, 2006 at 12:56 pm

#175090
Hello,
I was looking for help in automating a DTS package.
Here is our current process:
1. Another department drops the formatted files (.txt) into a directory on the network.
2. A notification is sent to me when the files are ready for importing.
3. Open and run the package.
Here is what I want:
The package runs if a new file is inserted into the directory on the network or new files are imported every 2 hours from that directory.
If someone could point me into the right direction, I should be ok.
Thanks,
James

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Well, the method for automating a DTS package usually consist of making it into a scheduled job and letting SQL Agent kick it off for you. I cannot see how you could trigger the job to run if a new file is inserted into a directory, but you could schedule the job to run every few minutes (or any increment you choose) and the job can check the directory before calling the DTS package.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Rob-188054 SSC-Addicted Points: 416 More actions · Answer 2

Hi James,

I found a good article about doing multiple files in a directory...

http://www.databasejournal.com/features/mssql/article.php/10894_3325731_1

But I think John's right - the best thing to do is to schedule the job up every 2 hours or so... unless you create a separate application to listen for new files in the directory.

Rob

Robert Davis One Orange Chip Points: 28027 More actions · Answer 3

I would put a bat file on the server, schedule the bat file via Windows Scheduled Tasks to check for existence of new files. If new files are detected, fire off the DTS package using the DTSRun executable.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Apollo74x Ten Centuries Points: 1250 More actions · Answer 4

Thanks all. I'll try these different options.

James

Bob Stahr SSC Veteran Points: 221 More actions · Answer 5

I recently wrote a slick SP that shells out and grabs the Directory information for a directory and loads all the details into a table. I could post that for you and others if that would help.