Using Perl within a DTS package.

Question

Using Perl within a DTS package.

rustroot1

Mr or Mrs. 500

Points: 560
More actions
July 22, 2002 at 10:42 am

#357575

Hello All,
I have a DTS package in which I will be using within a job. This package will execute a Perl Script. When I create a WIN32 execution task to execute the Perl script, the DTS package executes as expected. However, when the job executes this DTS package, the job hangs on step 1 and never seems to fail.
I have tried to execute the Perl Script within the job itself, but I get the same result. I have the correct permissions to run the perl script.
Can anyone else offer some ideas to get the job to execute this perl script?
Thanks in Advance,
Rusty

Viewing 13 posts - 1 through 12 (of 12 total)

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

Steven. SSCrazy Eights Points: 9806 More actions · Answer 1

Are you running the perl script via the sp_cmdshell sp.

If you do, does the sqlagent allow this (ie the checkbox in properties), also are you running this job as an admin account or do you have a sql proxy account setup

Steven

rustroot1 Mr or Mrs. 500 Points: 560 More actions · Answer 2

Within the DTS package the step is setup as a WIN32 task. If this uses xp_cmdshell, then yes, but I am not sure what DTS uses to execute WIN32 tasks.

Within the job, I have tried running just the DTS package and using xp_cmdshell to execute the perl script. Both produce the same result - hanging on step 1. The job is running as an sa equivalent.

Thanks,

Rusty

Steven. SSCrazy Eights Points: 9806 More actions · Answer 3

DTS is a client side tool. Have you got perl installed on the pc you are running the dts from

Steven

rustroot1 Mr or Mrs. 500 Points: 560 More actions · Answer 4

Yes, Perl is installed on both the client and the server. The problem is not executing the DTS package. It is getting the job to execute the package successfully.

When I manually execute the DTS package, the package executes as expected. When the job runs the step that executes the package, it hangs and never produces any type of failure or error message.

Steven. SSCrazy Eights Points: 9806 More actions · Answer 5

How about testing it with a test perl script.

Something which writes to a log file. That way you can test if the perl is executing correctly.

Steven

rustroot1 Mr or Mrs. 500 Points: 560 More actions · Answer 6

Ok... I have added this to the script, and when the DTS package is executed, the log file is generated. When the job is executed, the job hangs and no log file is produced.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 7

Do you have either physical or terminal server access to the server?

K. Brian Kelley

bkelley@sqlservercentral.com

http://www.sqlservercentral.com/columnists/bkelley/

K. Brian Kelley
@kbriankelley

sokolove Mr or Mrs. 500 Points: 546 More actions · Answer 8

Try creating a batch file that executes the perl script and run the batch file from your DTS package. This works for me. Here's the batch file I use

@Echo Off

c:\perl\bin\perl.exe c:\scripting\userinformation.pl

Edward M. Sokolove

Edited by - sokolove on 07/29/2002 07:48:14 AM

Edited by - sokolove on 07/29/2002 07:48:43 AM

Edward M. Sokolove

rustroot1 Mr or Mrs. 500 Points: 560 More actions · Answer 9

Yes. I have physical access to the server... and I have the perl script being executed via a batch command. The DTS package executes the batch script fine. The problem is when I try to schedule the DTS package to run as a job.

sokolove Mr or Mrs. 500 Points: 546 More actions · Answer 10

Yes. I have physical access to the server... and I have the perl script being executed via a batch command. The DTS package executes the batch script fine. The problem is when I try to schedule the DTS package to run as a job.

I had the same problem. It ended up I was using J:\whatever\whatever when I changed it to the specific address or UNC(Universal Naming Convention) \\servername\directory\specific file then it worked, because J:\ was on my system but the server did not know what I was talking about. So in your DTS packages look at your connection part of the dts package and change that address and everything should work.

Edward M. Sokolove

YeePee Mr or Mrs. 500 Points: 561 More actions · Answer 11

I think the Problem is with SQL Agent. Make sure that the Account used to Start the SQL Agent has got Admin Rights on the Local Box. As a general rule ,do not start Local system for starting SQL Server agent.Use a Domain account with Enough Privilages on the box to start SQL server Agent.This should solve the problem.

rustroot1 Mr or Mrs. 500 Points: 560 More actions · Answer 12

Thanks to all that have helped with the problem. Below is what the problem was.

The perl script being executed downloaded several files via FTP from a mainframe system. The download is very large and takes about 45 min. to complete. When running this script via .BAT file through DTS, everything ran fine.

When this same DTS package was scheduled to run using the SQLAGENT, the job appeared to be hung on step 1. I say it appeared to be hung because there was no log file being generated and the progress of the files being downloaded was no where to be found.

Little did I know that these files were actually being downloaded, just not in the location I thought. The files were being downloaded in the %MSSQL_HOME%\BINN directory. This directory is where the sqlagent.exe is started. So to fix the problem, I had to script the .BAT file to change directories to where I wanted the files to be placed.

Once Again, Thanks to everyone who gave their advice.

Rusty