DTS round robin - SQL Server Forums

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)

DTS round robin

Author

Topic

lauramayer
Posting Yak Master

152 Posts

Posted - 2005-06-22 : 14:31:26

Afternoon,

I have a DTS package that runs via a stored proc that's called on the webserver. It says it running and it's executing all steps but it's lying :) The file is never updated on the webserver.

I broke it up into The first step takes data from a holding table and exports it to a text file on the database server, then the next step it to take that text file and FTP'S it to the webserver. The folder on the webserver is shared and has permissions for domain users to modify the contents. The first step works fine, the second step says it completed without errors but nothing goes to the file.

Is there something else I should be looking at? I'm open to any and all suggestions.

Thanks

Laura

SteveTR
Starting Member

7 Posts

Posted - 2005-06-24 : 09:26:10

Laura,
I assume your DTS works fine if you launch it from Enterprise manager. See if it also works if you schedule it using SQL Agent. That runs under it's own security context. If that fails, then you have a rights issue.

You mention that step 1 works fine... so your extracted file makes it to the Database servers file system? and FTP is the problem? Getting to the file system should be the hard part because normally you don't let a web client of your database access to the db server file system. (shades of the slammer worm)

But I digress...

I had to do this about a year ago, and after serching the web found this approach:

1) Create a SQL Server Agent job "MyJobName" which runs the DTS package (test this job and make sure it does what you want)
2) Assign rights to "MyJobName" to the account your web server is using to access the database (test again, Job rights are different than DTS rights)
3) Create a stored procedure to lauch the job:
CREATE PROCEDURE sp_launch_myjob
AS
exec msdb.dbo.sp_start_job
@job_name = 'MyJobName',
@server_name = 'MYDBSERVER'
GO

4) Have your web page run sp_start_job to launch the Job using the SQL Server Agent.

5)You can then query the SQL Agent status and inform the user when it is complete:
CREATE PROCEDURE sp_job_status
AS
exec msdb.dbo.sp_help_job
@job_name = 'MyJobName',
@job_aspect = 'JOB'
GO

6) Since DTS doesn't come with an FTP send object (Hey Microsoft, major pet-peeve here!) I shell out to the built in FTP.exe that comes with windows, and script it to FTP send files.

With this approach, the DTS package runs under the Agent's security context, which is probably the issue with launching it directly.

Hope this helps.
- Steve TR

lauramayer
Posting Yak Master

152 Posts

Posted - 2005-06-24 : 10:46:50

Thanks Steve I'll try that. I also found a post that uses a command line FTP utility so I'll try that too.

Thanks again.

Laura

lauramayer
Posting Yak Master

152 Posts

Posted - 2005-06-24 : 11:09:16

Okay now I'm confused. Again the first part works, the passing of data to a text file on the database server. The second step

EXECUTE master..xp_cmdshell 'ftp -n -v -i -s:d:\FTPSettings\FTPSettings.txt'

works in query analyzer but not as part of the package.

The ftp settings file is simple as well:


open servername
user cswg\lm0406 password
ascii
put D:\FTPSettings\ExportSAR.txt 
bye

obviously servername and password are correct in the FTP settings file.

Any ideas? I even created a job to run the DTS package and made the owner the same owner as the one that has rights on the webserver. The put command references a directory on the database server.

SteveTR
Starting Member

7 Posts

Posted - 2005-06-24 : 12:59:30

Hi Laura,
Not sure why that doesn't work. Instead of xp_cmdshell I used the "Execute Process Task" widget in DTS to run "c:\ftp\doftp.cmd" which contains the following:
ftp -i -s:c:\ftp\ftp_cmds.txt

ftp_cmds.txt contains:
open destservername
myusername
mypassword
ascii
put sourcefile.dat
close
bye

Would you mind sharing how you launched the DTS from Stored procedure w/o the job approach?
Thanks,
- Steve TR

lauramayer
Posting Yak Master

152 Posts

Posted - 2005-06-24 : 13:29:16

Not at all. I used this: http://www.pengoworks.com/index.cfm?action=articles:spExecuteDTS and ran it on a webpage using php. I got the silly thing to work finally. I ended up doing just what you said and now it works perfectly.

It works pretty much as advertised. I don't think I changed anything drastic.

Good luck and thanks again.

Laura

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources