problem running a SQL scheduled job calling a DTS in remote desktop.

Question

problem running a SQL scheduled job calling a DTS in remote desktop.

999baz

Ten Centuries

Points: 1053
More actions
September 16, 2009 at 10:43 pm

#209945

Hi folks,
for fortnighly refresh i copy 2 zip files from an FTP site to a Remote destop.
from these Zip files i extraxt+save two .txt files into theri respective sublfolders in this remote desktop.
F:\UploadFolder\SUbfolder1\File1.txt
F:\UploadFolder\SUbfolder2\File2.txt
After that i schedule an Upload job in SQL 2005 to run overnight, this job basically calls a DTS Upload package in the remote desktop and uses the above saved files via connection managers named File1.txt and File2.txt.
Now my problem started about a month ago when the sysytem adminstrator as part of his serurity audit took away access rights to the UploadFolder in the remoter desktop.
Since then the SQL Scheduled job has failed each time with the same error message:-
Message
Executed as user: XXXX\idstartupusersql. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 9:50:00 PM Error: 2009-09-16 21:50:01.84 Code: 0xC001401E Source: UploadFile1 Connection manager "File1.txt" Description: The file name " F:\UploadFolder\SUbfolder1\File1.txt" specified in the connection was not valid. End Error Error: 2009-09-16 21:50:01.84 Code: 0xC001401D Source: UploadFile1 Description: Connection "File1.txt" failed validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:50:00 PM Finished: 9:50:02 PM Elapsed: 1.203 seconds. The package execution failed. The step failed.
I get this for both files, even though all rights/permissions to all the folders in the remote desktop have been restored.
BUt when I manually run the Uploadfile1 or UPloadFile2 DTS package in remote desktop it all works perfectly , however each time i try run DTS from the SQL 2205 scheduled Job it brings about a failure, which I suspect was due to Job owner: XXXX\idstartupusersql account not having the required permissions/rights etc. BUt even though those permission are restored the error message remains the samne.
Connection managers for bothe files in DTS are OK since runnig them manually works fine.
LET ME KNOW WHAT YOU THINK may be causing all this?

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

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

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

I'm not clear about where everything is located. Are the upload job and upload package on the same server as the .TXT files? If not, change the connection managers to use a UNC file path rather than a drive letter.

Greg

999baz Ten Centuries Points: 1053 More actions · Answer 2

Upload job + remote desktop on the same server ie:-

123.456.789.123,4444 --> Upload Job

//123.456.789.123/Upload$\TheTwoTextFiles.txt -->Remote desktop.

SSIS packages arealso on the remote desktop.

Have Changed the connection mangers to use DNC paths

\\server\share\file_path

//123.456.789.123/Upload$\File1.txt

//123.456.789.123/Upload$\File2.txt

Still the error message for the Upload JOb is the same as in my original post.

A thought, IN Upload job step properties where it specifies where the package is should i also make it DNC instead of Drive name:-

ie :- E:\MSQL2005\MSSQL.3\MSSQL\scripts\FOLDER1\BulkUploadFolder\PackageForTextFile1.dtsx

make it DNC

//123.456.789.123/\MSQL2005\MSSQL.3\MSSQL\scripts\FOLDER1\BulkUploadFolder2\PackageForTextFile2.dtsx

have also tried this but no luck.

Error Message:-

Failed to open :

"//123.456.789.123/\MSQL2005\MSSQL.3\MSSQL\scripts\FOLDER1\BulkUploadFolder2\PackageForTextFile2.dtsx

" due to error 0x80070003 "The Sysytem cannot fing path specified"

Daniel Forrester 123 SSCrazy Points: 2276 More actions · Answer 3

Just in case you hadn't noticed you have your slashes the wrong way around on the UNC.

//123.456.789.123/Upload$\File2.txt

ie

\\123.456.789.123\Upload$\File2.txt

999baz Ten Centuries Points: 1053 More actions · Answer 4

Thanks, posted in haste.

Anyway, some of my problem was solved, it was clear from the outset that this a user rights issue.

so initially the step was executed under the account used by the sql Server Agent service since the job owner was an account in the sysadmin fixed server role.

I simply created a proxy Account to schedule the job and asked sysadmin to give this account the required security rights. After this was able to schedule the job and DTS package runs ok but in Log file Viewer under view history am still getting the package execution returned DTSER_FAILURE (1). error, i hvae no idea why this happens even though the FILE1 + file2 runs to completeion and send me 2 confirmation emails signifying the refresh took place ok.