February 10, 2004 at 9:42 am
I have a DTS Package in SQL Server 2000.
Purpose: Copy a File from one server to another
1. The Package uses VBScript to Clear the File I want to transfer
2. The Package Connects to two SQL Server 2000 and Copys a Table From Server1 To Server2 using Transform Data Tasks
The Problem i am having is that the Package works great when I execute the Package but when i Schedule the package i keep getting an error.
SQL Server Scheduled Job 'test' (0xBEB06403374CE144A25BCC22E1566979) - Status: Failed - Invoked on: 2004-02-10 00:00:00 - Message: The job failed. The Job was invoked by Schedule 25 (test). The last step to run was step 1 (test).
Please Help
Thank You
Keith Hinkle
February 10, 2004 at 10:00 am
How do i find out
February 10, 2004 at 10:01 am
Hello, this could be caused by secuity Permissions. Try running the package logged in as the SQL agent account. Myles
February 10, 2004 at 10:05 am
how do i log on as a different user
February 10, 2004 at 10:07 am
Hello all. When running DTS packages from SQL Jobs, the security context is SQL agents. If SQL agent does not have permission between the two servers or is running under local account the job will fail. This is of course only when you use trusted connections. Hope this helps. Myles
February 10, 2004 at 10:08 am
Hello Keith, you have to logout of your windows session and log back in under the SQL agent NT service account.
February 10, 2004 at 10:16 am
I Logged in as a differnet User and i am still getting the error
February 10, 2004 at 5:21 pm
Hello Keith,
did you login using the SQL agent service account and run the package manually?
Also you may want to turn the logging on for the dts package. you can out put the error message to a text file.
To set logging. Open the DTS package right mouse click on the design sheet select package properties and select the logging tab. Set the location of the text file, etc.
Let me know what the text file says.
Myles
February 11, 2004 at 5:59 am
I would also suggest changing the job to use the dts name rather than the dts guid.
It could be a case where you made a change to the dts after scheduling the job and it's trying to use the old version.
Use the syntax
DTSRUN /S<servername> /N"<package name>" /E
This uses trusted authentication (ie the server account of sql server agent).
February 11, 2004 at 6:18 am
Ok first off we haven't really decided it is a permissions issues that is the cause of the failure. Then message you see is normally longer in the Job History. YOu might have to view the step details to get a better idea. VIew that and post the details from the step that failed.
Now could be this is all you have to go on so far so let's start here.
You say step 1 in the package (not job) is
"The Package uses VBScript to Clear the File I want to transfer"
Where is this file located local to server, remote?
How is the file transfered, FTP, copy,other?
When you tested the package did you test on the server itself or from your workstation or other location?
If other location or workstation keep in mind that this causes all the exectuion of connections and transfering across that physical machine not the server. Test as you from the server, if fails may give you a better idea. If doesn't then narrows the field on the source of the issue.
As for permissions and the account SQL Agent is running under use Services control panel and find the SQLServerAgent item, view the properties and see what account it is set to run under. That is the account that the permissions matter if it is needed to log into a remote source.
February 11, 2004 at 6:43 am
Hello All. I agree we have not found the true cause of the issue. Just the permissions are the most likely cause of failure from what Keith has outlined. Remember the DTS package runs fine in the designer. This could also be a package version issue. Keith could reschedule the package using the DTSrunui.exe to make sure the SQL job is calling the latest version. The best thing is for Keith to give the forum more details of the error message. To do this review the SQL job history for the failed job and check the checkbox for show step detail. This will give us a better understanding of the problem. Myles
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply