Executing a DTS Package from an ASP page

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Executing a DTS Package from an ASP page

Author

Topic

jwcoffield
Starting Member

1 Post

Posted - 2002-08-14 : 09:32:29

I have a comma delimited text file that I need to import into a SQL2K database every day. This file is not available at a standard time each day which makes scheduling the DTS task difficult. I want to allow an operator to run the DTS task as soon as the file is available, but I don't want to necessarily let him fully into Enterprise Manager. Is there a way that I can create an ASP page which will fire off the Execute command to the DTS task?

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-14 : 09:39:07

create dts package
create agent job to run dts package
create proc to start agent job
create asp page to execute proc

Jay White
{0}

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-14 : 09:39:52

Sure, you can create a job that runs the DTS package, and the operator would only need to run the job (using sp_start_job, see Books Online for more info)

Getting the file to the SQL Server is probably the hardest part, because it (most likely) won't be able to access it on the client computer. Where does the file reside now anyway? If it's on a personal PC, you can add some code to upload it to the SQL Server, or web server, or some network computer that SQL Server can access using UNC paths.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-14 : 11:06:00

You can schedule a job that runs every 15 mins or so.
It looks at the directory - if the file is there it runs the next step which tis the import.
Next step is move file to archive directory.
Another job (or first step of this one) looks to see if this has completed by a certain time - if not sends out an alert.

I prefer to run dts packages from VB (called from the job) as you can then log a lot of properties on an error.

Better to copy the file to the directory then rename it, especially if ftp is involved - you won't get problems with the file being locked.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-14 : 11:06:01

sameerv
Starting Member

29 Posts

Posted - 2002-08-17 : 10:35:24

I have a similar problem. i.e. I have made a DTS package which reads the file from a folder on the IIS Server and imports the data into an SQL 7.0 table. But, although the file to be imported will be picked up from the same directory, there are multiple files in the folder. The file name of the file to be imported will be saved and retrieved from a table.
My problem is : how do I send a filename as an input parameter to the DTS package before executing the package ???

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-08-17 : 10:48:09

You can use global variables for this and/or Dynamic Properties Task (if on SQL 2000). Check out BOL and [url]www.sqldts.com[/url]

HTH
Jasper Smith

sameerv
Starting Member

29 Posts

Posted - 2002-08-18 : 03:41:28

Hi Jasper,
Can these options be used in SQL Server 7.0 also ??

sameerv
Starting Member

29 Posts

Posted - 2002-08-18 : 03:43:52

Jasper,

For your information the link that you suggested I go to (www.sqldts.com) is a dead link.