Executing DTS package from QA
-
I am creating a DTS package to transfer data from a Text file to SQl server 7.0. I created the packages with my userid. The text files are in my C:\. I use the full path while refering to the text file (e.g.,\\systemname\C$\Folder). The package runs fine when i do Execute package in the DTS designer. When i try to run it from QA using xp_cmdshell, i get the following
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSActiveScriptTask_1
DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147217887 (80040E21)
Error string: Error opening datafile: Access is denied.
Error source: Microsoft Data Transformation Services Flat File Rowset Provider
Help file: DTSFFile.hlp
Help context: 0
This is the first time i am working on it. What am i missing.
-
How are you defining your connection to the dts package in DTSRUN -E for trusted or a named account. If the later or even the first it may not have proper access to the item in question.
-
Thanks for your quick reply. This is how i run it in QA.
Master.dbo.xp_cmdshell 'DTSRUN /S Servername /E /R /N packagename'.
How do I proceed.
-
Ok when you run it thru EM are you executing from your machine or the server. If you machine then keep in mind xp_cmdshell will actually execute it on the server. If that i the case then something about your permissions on the server itself in regards to your user account. Try running from EM on the Server and you should get the same error.
-
I was running in EM on my machine earlier. I tried on the server machine now. It worked fine in EM and gave the same error in QA.
-
Is SQL itself running under Local System Account as it's service provider account or a Machine account. I want to say that maybe using it this way actually uses the trusted account of the SQL Server and if running under local system may be an issue. Let me check on this and test at work to see if I can duplicate. To help me out can you post the concept of your DTS package so I understand what I need to create to test?
-
I have a delimited text file on my C drive. I have Active x script task which checks a table to see if i already copied the file. ( this works)and sets the next file to read as a global variable. On success I have a Data pump task. In the work flow properties, I set the source file.
Set ObjFSO = CreateObject("Scripting.FileSystemObject")
szNewSourceObjectName = DTSGlobalVariables("szWorkingFolder").Value & "\" & "datafile.txt"
Set oConnection = oPKG.Connections("Text File (Source)")
' Set Filename to new value
oConnection.DataSource =szNewSourceObjectName
Set oStep = oPkg.Steps("DTSStep_DTSDataPumpTask_2")
For Each oTask in oTasks
' Check for DataPump or DDQ Task
If oTask.CustomTaskID = "DTSDataPumpTask" Or _
oTask.CustomTaskID = "DTSDataDrivenQueryTask" Then
' Get CustomTask Reference
Set oCustomTask = oTask.CustomTask
' Amend Source Object Name
If oCustomTask.Name = "DTSTask_DTSDataPumpTask_1" Then
oCustomTask.SourceObjectName = szNewSourceObjectName
IF objfso.FileExists( szNewSourceObjectName ) Then
IF ObjFSO.GetFile ( szNewSourceObjectName ).Size = 0 Then
' Update the following task's precedence constraint
' - Execution status of inactive
oStep.PrecedenceConstraints(1).PrecedenceBasis = DTSStepPrecedenceBasis_ExecStatus
oStep.PrecedenceConstraints(1).Value =DTSStepExecStat_Inactive
Main = DTSStepScriptResult_DontExecuteTask
Exit Function
End If
End If
Exit For
End If
End If
Next
And then the actual data transfer.
Hope i am clear.
-
When you run the package through the Designer it's running on your workstation with your security context. Therefore it will be able to access the file via the C$ share.
When you execute via xp_cmdshell the package is executing under the security context of the server. As you're getting an "Access Denied" message, I'd say the server doesn't have sufficient privelages to access the file via the C$ share.
Thanks
Phill Carter
--------------------
Colt 45 - the original point and click interface
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply