DTS pkg is not running when we logged off from the server
-
Hi All,
I am facing a long runing DTS issue.
Initially server running under some user acc' & the DTS pkg exec sucessfully.
Recently we have changed the log on acc' to domain acc' till then DTs exec failing.
Pkg details:
DTS generating reports in some other server.
log on acc & DTS owner both are same and it domain admin acc'.
and the Owner is having sysadmin & db_owner permissions.
but DTS execution is failng.
currently i am executing this DTS pkg with windows scheduler, there is also one problem occured.
It is executing successfully when i am logged into that server.
Once i logged out from the server, it is failing.
Note: i havent checked the "Run only if logged on" option in windows scheduler.
Please help me out guys...
-
So the DTS package is set up to run reports on another server?
I'm assuming it's set up as a linked server?
Does the account you replaced the original one with have permissions on the remote server to execute that part of the package?
Small chance if you disabled the account previously used that a linked server used their credentials?
Longshot but worth checking...
If you can elaborate a bit more I might be able to help you narrow it down anwyay 🙂
-
Thanks for the reply Ritchie...
I havent used the linked server concept here.
Once again i'm explaining everything...
DTS consists 13 Tasks(3 activeX + 10 datapump tasks)
1st step: In the first step(ActiveX script) it will create 10 blank excel files "remote mechine".
2 to 11 steps: Next 10 tasks(Data Pump Tasks) inserts the data into those excel files.
12th step: ActiveX script create one new excel file and adds the above created 10 excel files into this newly created excel file as a Sheets.
*Note:
1.All these excel files are created on the remote server.
2. Sql Server logOn acc' & SQL Agent acc' & DTS owner & Job owner are the same and it is domain admin acc'( this acc' is admin on both servers).
And now the Issue is:---
2 months back we have changed the sqlserver & agent logon acc' to new one(Domain acc') it is having all admin rights on OS & SQLServer.
But till then DTS package is not running through SQL agent jobs.
manually it is running fine.
This is one issue..
and the other thing is ....
Now i have scheduled this pkg in windows scheduler.
It is running fine when i am logged into the server.
Once i logged off from the server it is failing .
*Note: i havent checked the "run only If Logged in" option in windows scheduler.
I think now you understand my prblm 🙂
-
OK, chances are you're trying to write to a share then, have you checked the permissions on the actual share, and that the designated account you're now using has permissions to write to/access that shared area?
What you're describing, I would definitely say appears to be a permissions issue anyway.
If it is not the permissions on the share, are you able to post the error message you're getting?
-
Hi,
hey i'm sorry for the big reply...:-)
Based on this reply u can undrstnd how big problem it is for me.
Below i'm copying the log:
*****
The execution of the following DTS Package succeeded:
Package Name: HRExports
Package Description: (null)
Package ID: {304280B5-7F04-4E11-A7B5-754E69C30B78}
Package Version: {5B9ECFE3-5228-4F75-8A72-2AEDA3E81D86}
Package Execution Lineage: {3416A191-87D1-4339-920B-32245D463266}
Executed On: GEXPRDCRDB01
Executed By: svc_sql
Execution Started: 11/18/2009 1:00:01 AM
Execution Completed: 11/18/2009 1:26:24 AM
Total Execution Time: 1583.859 seconds
Package Steps execution information:
Step 'DTSStep_DTSDataPumpTask_1' succeeded
Step Execution Started: 11/18/2009 1:00:04 AM
Step Execution Completed: 11/18/2009 1:00:09 AM
Total Step Execution Time: 4.937 seconds
Progress count in Step: 70
Step 'DTSStep_DTSDataPumpTask_2' succeeded
Step Execution Started: 11/18/2009 1:00:09 AM
Step Execution Completed: 11/18/2009 1:00:09 AM
Total Step Execution Time: 0.344 seconds
Progress count in Step: 6
Step 'DTSStep_DTSDataPumpTask_3' succeeded
Step Execution Started: 11/18/2009 1:00:09 AM
Step Execution Completed: 11/18/2009 1:00:13 AM
Total Step Execution Time: 3.703 seconds
Progress count in Step: 392
Step 'DTSStep_DTSDataPumpTask_4' succeeded
Step Execution Started: 11/18/2009 1:00:13 AM
Step Execution Completed: 11/18/2009 1:18:29 AM
Total Step Execution Time: 1095.719 seconds
Progress count in Step: 5623
Step 'DTSStep_DTSDataPumpTask_5' succeeded
Step Execution Started: 11/18/2009 1:18:29 AM
Step Execution Completed: 11/18/2009 1:18:37 AM
Total Step Execution Time: 8.094 seconds
Progress count in Step: 55
Step 'DTSStep_DTSDataPumpTask_6' succeeded
Step Execution Started: 11/18/2009 1:18:37 AM
Step Execution Completed: 11/18/2009 1:18:40 AM
Total Step Execution Time: 2.781 seconds
Progress count in Step: 0
Step 'DTSStep_DTSDataPumpTask_7' succeeded
Step Execution Started: 11/18/2009 1:18:40 AM
Step Execution Completed: 11/18/2009 1:18:43 AM
Total Step Execution Time: 3.813 seconds
Progress count in Step: 382
Step 'DTSStep_DTSDataPumpTask_8' succeeded
Step Execution Started: 11/18/2009 1:18:43 AM
Step Execution Completed: 11/18/2009 1:18:44 AM
Total Step Execution Time: 0.187 seconds
Progress count in Step: 3
Step 'DTSStep_DTSDataPumpTask_9' succeeded
Step Execution Started: 11/18/2009 1:18:44 AM
Step Execution Completed: 11/18/2009 1:18:57 AM
Total Step Execution Time: 13.328 seconds
Progress count in Step: 2173
Step 'DTSStep_DTSDataPumpTask_10' succeeded
Step Execution Started: 11/18/2009 1:18:57 AM
Step Execution Completed: 11/18/2009 1:26:00 AM
Total Step Execution Time: 423.391 seconds
Progress count in Step: 1268
Step 'DTSStep_DTSActiveScriptTask_1' failed
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:ActiveX Scripting : Error parsing script
Step Error code: 800403FB
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:4500
Step Execution Started: 11/18/2009 1:26:00 AM
Step Execution Completed: 11/18/2009 1:26:24 AM
Total Step Execution Time: 23.922 seconds
Progress count in Step: 0
Step 'DTSStep_DTSActiveScriptTask_2' succeeded
Step Execution Started: 11/18/2009 1:00:01 AM
Step Execution Completed: 11/18/2009 1:00:04 AM
Total Step Execution Time: 3.531 seconds
Progress count in Step: 0
Step 'DTSStep_DTSActiveScriptTask_4' failed
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: Permission denied
Error on Line 27
Step Error code: 800403FB
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:4500
Step Execution Started: 11/18/2009 1:26:24 AM
Step Execution Completed: 11/18/2009 1:26:24 AM
Total Step Execution Time: 0.109 seconds
Progress count in Step: 0
***
From the we can know that the failure in the Active script.
It is stating that permissions denied but for the owner (Agent logOn acc' both are same & have Admin Privilages on OS) i have assigned all server roles & sysadmin in Databaseroles. So what else do i need to assign..
And the actual issue is in 2nd activeX script, it is failing to complete it's execution.
while adding the 10 excel sheets in to the single report file, as separate sheets it is failing and the excel instance remains in running state in TaskManager.
Result is Job failure.
And one more thing when i have change the sheet name it executing properly(i.e not accepting the sheet name in the excel) ..
below is the peice of Vbscript of that.
et objwbIndividual = objExcel.Workbooks.Open("\\gexprdcrapp02\Apps\HRExports\Parallel_Run\New Hires-Corp.xls")
Set objWSIndividual = objwbIndividual.Worksheets("New_Hires_Corp")
set objWSHRGroupReport = objwbHRGroupReport.Worksheets.Add ( , objwbHRGroupReport.Worksheets(objwbHRGroupReport.Worksheets.Count))
objWSHRGroupReport.Rows("1:1").EntireRow.Font.Bold = True
Set xlrng = objWSHRGroupReport.Columns("B:B")
xlrng.NumberFormat = "m/d/yyyy"
objWSHRGroupReport.Range("A:Z").Value = objWSIndividual.Range("A:Z").Value
objWSHRGroupReport.Columns("A:Z").EntireColumn.Font.Name = "Verdana"
objWSHRGroupReport.Columns("A:Z").EntireColumn.Font.Size = 8
objWSHRGroupReport.Columns("A:Z").EntireColumn.AutoFit
''objWSHRGroupReport.Name = "New Hires - Corp"
objWSHRGroupReport.Name = "PQRS"
objwbIndividual.Close False
set objwbIndividual = nothing
Here it is not accepting the sheet name--- "New Hires - Corp"
Other than this it accepting any thing like "PQRS", "ABCD"...etc...
But why it is not accepting actuall file name .....
Thanks for reading whole story 🙂
-
Did you check the share permissions for the destination folder you're dropping the excel sheets into?
It is most definitely an issue with the account you're using to run the job, and permissions they have on the destination of the files you're generating.
Make sure to have the account as an (OS Level, not SQL level) administrator on the source and destination servers, and check the share permissions (If you set them up as an administrator on the server this shouldnt matter).
Have you tried logging on as the user you have setup to run the package and manually running the package to confirm?
You obviously have permissions over and above the specified account if you can manually run it while it cannot.
If you log onto the destination server as this account, and follow through the steps and destinations of all files created then it should be quite straightforward to work out which area has the permission problem.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply