DTS Scheduled Task Fails - Permission Denied

I searched the forum's threads on this, and while there were many results, none have helped so far.

I am running a DTS package that is an ACtiveX Script Task using VBScript. The script uses CreateObject() to create a FileSystemObject to copy an .MDB before importing the tables into SQL Server. I want to copy it because of Access' notoriety of corrupting, and this much data being pumped out of Access could force me to Compact & Repair. I would rather do that on a copy.

Function Main()

Dim FSO
Set FSO=CreateObject("Scripting.FileSystemObject")

FSO.CopyFile "\\<server>\<Share>\Data.mdb", "\\<server>\C$\DataCopy\Data.mdb"

Main = DTSTaskExecResult_Success
End Function

The DTS Package runs when I execute it from Ent. Manager, of course. It fails if scheduled, or course :(

I have set the Owner of the Scheduled Task to my domain account, which is also in the Adminstrators Group on the physical server with the SQL Server installation (Windows 2003 Server). I also did the unnecessary task of adding my domain account specifically to the destination folder, which is also Shared.

My sqlagent.exe service runs as SYSTEM on the server, so the SQLAgent should have no problem copying a file from one folder on the server to another.

The Scheduled Task fails with the common error:

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSActiveScriptTask_1
DTSRun OnError: DTSStep_DTSActiveScriptTask_1, Error = -2147220482 (800403FE)
Error string: Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: Permission denied
Error on Line 12
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500
Error Detail Records:
Error: -2147220482 (800403FE); Provider Error: 0 (0)
Error string: Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: Permission denied
Error on Line 12
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 4500
DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1
DTSRun: Package execution complete.

I checked this MS KB Article (http://support.microsoft.com/kb/q298725/), but the instructions after opening DCOMcnfg.exe do not follow what is shown in the WMI window on Windows 2003 Server :rolleyes: (i.e. there is no "Default Security" tab to click.)

I am at a loss here. Thanks for your help.I'll bump this once.|||I'm still working on it so not sure if what I suspect is the issue... but have a dts package that download a file, based on SQL statement from as/400 to sql table... runs great when I run it myself within the enterprise manager but when I try to call from a stored procedure via my exe it fails to run.

What I've deduced is that when I run it within the enterprise manager that it is, in essence, running on the client... locally and using my dll's, etc... however, when I run via stored procedure through my exe it is trying to run on the server, herein lies the problem (really strong guess at this point)... the server is not set up to connect to the as/400 and that's where it bombs.

I'm guessing that your sql server is having issues talking to your access database in much the same way that mine is having issues talking to the as/400. I've got the client software installed now and will be testing my theory shortly. Hope this helps you with your issue.

b|||Is the access db on the same server as Sql server?

If so, do non unc paths work? Such as :

FSO.CopyFile "c:\somefolder\Data.mdb", "c\DataCopy\Data.mdb"|||.. I have a package as well.
The thing is that if I put in a msgbox to execute directly before the ".deletefile", the package will run, but if I outcomment It it fails...
I can on the other hand do an ".copyfile" instead...
I only have the problem with the ".deletefile"
I would also appriciate all help i can get on this.

// selander

Function Main()

Dim Eso, E, E1, Ec
Set Eso = CreateObject("Scripting.FileSystemObject")
Set E = Eso.GetFolder("\\CLUST\SYS\IMPORT\ENT\")
Set Ec = E.Files
For Each E1 in Ec
if ucase(E1.name) = ucase(DTSGlobalVariables("strFileName").Value)+".ZIP" then
msgbox "FUBAR"
Eso.DeleteFile "\\CLUST\SYS\IMPORT\ENT\" + E1.name , True
exit for
end if
Next
Main = DTSTaskExecResult_Success
End Functionsqlsql

DTS question

Say I would use a ActiveX vbScript to query an LDAP server.

If i then would use that script to run in a DTS package. Witch steps do I have to take? Do i need to make a connection with the database, how do I create links.

I not looking for an out of the box solution, just maybe some good links to good sites witch explain this problem / question. My search wasn't that successfull

Greetings,
GodofredoYour DTS may contain nothing but an ActiveX script. You create connections inside the script using ADO, RDO, or whatever else you think you can use for it.|||yes but i want to fill up a table with the information retrieved by the ActiveX script|||you could just create a job that runs your activex script by querying ldap
and you could make that available to the next available step in the job. you dont really have to run dts. BUt if you want to, SQL Server has access to the ADSI oledb provider . in other words you could link a server to active directory and run a query based on your appropriate LDAP architecture.|||This is what a call a straightforward answer

Thanks,
Godofredo|||Hey, that's because he teaches this stuff...

Scott does DTS and LDAP...|||"at the same time baby!!!!"

is this thing on ?? 1.. 2.. 3 ...sqlsql

DTS problem: ActiveX Script Task Properties dialog box

My ActiveX Script Task Properties dialog box has the "functions" window
maximized and I
can no longer get at my code.
I can not find any way to resize it.
Please ...
If anyone has come across this and can help...
it will be much appreciated.
I've never had this happen before.
Thanks in advance,
bob mcclellanDid you find out the fix for that dialog box problem?|||I went to C:\Program Files\Microsoft SQL Server\80\Tools\Binn directory
and Registered ALL dll's
*** Sent via Developersdex http://www.examnotes.net ***

DTS Package problem?

I have set up a couple of DTS local packages to run ActiveX scripts creating XML files and copying them to our webserver (on the same domain).

I can run them OK manually via the 'Execute' package command in the drop down list when I right click on them and also get the 'Package successfully run' message back on running them, but when I schedule them to run overnight I find the SQL Server Agent reports them as 'failed' .

What am I doing wrong?

This looks like a permissions issue...do you have a proxy account setup for the SQL Agent? Look here for info...http://www.support.microsoft.com/kb/890775

Brad Feaker

Ex nihilo, nihil fit

|||As referred if you are scheduling the DTS package then make sure to check privileges for the SQLAgent service account on those process, you can start the SQLAgent using a local account with Admin privleges to complete the task.

DTS package prob

I wrote next ActiveX script. But it doesn't work. Can somebody tell me what i'm doing wrong. It should retrieve the mail and Assoc-NT-Account, but so far i only get errors?

Another question: should I explicitly write this to a table? If yes, how?

************************************************** ******************** ' Visual Basic ActiveX Script '************************************************* **********************

Function Main() Main = DTSTaskExecResult_Success

strServerName = "**********" set oConn = CreateObject("ADODB.Connection") set oCommand = CreateObject("ADODB.Command") set oRS = CreateObject("ADODB.RecordSet")

oConn.Provider("ADsDSOObject") on error resume next oConn.Properties("User ID") = "*********" oConn.Properties("User Name") = "*******" oConn.Properties("Password")="*********" oConn.Properties("Encrypt Password")= True oConn.Open "Ads Provider"

set oCommand.ActiveConnectection = oConn

strQuery =" Select Assoc-NT-Account, mail from LDAP://servername/o=orgName/ou=OrgUnit where objectClass = 'person' order by cn"

oCommand.CommandText = strQuery oCommand.Properties("Page Size") = 99

set oRS.sort ="cn"

i = 0 While not oRS.eof vObjectClass=oRS.Fields("objectClass") bShow = oRS.Fields("mail") > " " if bShow then oRS.Fields("mail") oRS.Fields("Assoc-NT-Account") End If oRS.MoveNext i=i+1 wend End FunctionCan you tell us what error you're getting?|||Yes, it would help to assess and refer to http://www.sqldts.com for reference on the active-x scripts.|||ActiveX can't create object ADODB :(|||I think you need to register the ActiveX components (OCX) on every workstation. You can download Setup/Install-Maker-Programs which are doing this job for you.

And ensure other PCs have the same version of the MDAC_TYP and http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbenlr98/html/vamsgCantCreateObject.asp for reference.|||He's attempting to run the ActiveX component from an ActiveX Script in a DTS package. If this is running as a job from SQL Server, then you don't need ADO on anything other then the SQL Server, and that should be installed by default. If you're executing the ActiveX Script in a DTS Package directly (as opposed to a job), then you have to have ADO on your desktop, which should also be installed by default.

Godofredo, does it tell you exactly which line it fails on? And I'm assuming your real code does not have the run-on lines as in the example originally posted, am I correct?|||No the run lines are due to formatting when copying the code... sorry for that.

It fails on the | set oRS = CreateObject("ADODB.RecordSet") |- part the part between the two pipes. Is it that what you mean?|||Hmmm...

Try replacing this line:
oConn.Provider("ADsDSOObject")
With this:
oConn.Provider = "ADSDSOObject"

Second, I don't know about the "User Name" property. In my connections to LDAP, I simply use "User ID"

Third, I don't think you need to specify "Ads Provider" when you open the connection. Just leave it as:

oConn.Open

For reference, here's some code I've written for connecting to LDAP. You may find it useful:

'We need to make ADO objects to execute a search against Active Directory
set oConn = CreateObject("ADODB.Connection")
set oCommand = CreateObject("ADODB.Command")
set oRS = CreateObject("ADODB.Recordset")

'We have to use the AD Provider to search against AD using ADO
oConn.Provider = "ADSDSOObject"
oConn.Properties("User ID") = strADUserID 'Username of the account with access to *search* AD
oConn.Properties("Password") = strADPassword 'Password of the above username
oConn.Open 'Open connection to the provider
set oCommand.ActiveConnection = oConn 'Set the active connection

'Execute a SQL query through the AD Provider to the AD server to find our the username that was typed in
'ADNamespace = LDAP: (for AD), WINNT: can also be used to authenticate against a NT Domain
'ADServer = Server or IP to the AD server
'ADBaseDN = What AD container do we start searching from
'objectClass = "user" This tells the search to ignore everything except for "user" objects
'sAMAccountName = This is the user ID we want to find
strQuery= "SELECT distinguishedName FROM '" & strADNamespace & "//" & strADServer & "/" & strADBaseDN & "' WHERE objectClass='user' AND sAMAccountName='" & strUser & "'"
oCommand.CommandText = strQuery
oCommand.Properties("Size Limit") = 1 'We only want one result - and there should be only one.
oCommand.Properties("Timeout") = 30 'Time out if it takes to long
set oRS = oCommand.Execute 'Execute the query

DTS package execution time

Hi - I currently have a DTS package that takes raw data from a SQL
table and inserts records into several tables via a custom ActiveX
transformation. The package uses logic to determine which table to
insert into and then calls DTSlookups to perform the inserts. The #
of records I'm working with is small (3,000-10,000). If I perform a
"test, the rows are actually inserted, and rather quickly. If I
execute the package through normal methods, the execution takes several
hours and often times out?
Any ideas'Running a DTS package within Enterprise Manager executes it locally on
the machine where EM is running. Running it using a schedule runs it
on the server where the agent service is running. Different machines
have different resources and are under different loads, and have
different distances from the data. Look for data traveling over the
network vs remaining local.
As you describe it, both the source data and the final destination of
the data are in SQL Server. Are they on the same SQL Server? If so,
did you consider using stored procedures? Keeping all the work
withing SQL Server itself has some performance advantages.
Roy Harvey
Beacon Falls, CT
On 27 Jul 2006 10:37:26 -0700, clawdaddy@.gmail.com wrote:
>Hi - I currently have a DTS package that takes raw data from a SQL
>table and inserts records into several tables via a custom ActiveX
>transformation. The package uses logic to determine which table to
>insert into and then calls DTSlookups to perform the inserts. The #
>of records I'm working with is small (3,000-10,000). If I perform a
>"test, the rows are actually inserted, and rather quickly. If I
>execute the package through normal methods, the execution takes several
>hours and often times out?
>Any ideas'|||clawdaddy@.gmail.com wrote:
> Hi - I currently have a DTS package that takes raw data from a SQL
> table and inserts records into several tables via a custom ActiveX
> transformation. The package uses logic to determine which table to
> insert into and then calls DTSlookups to perform the inserts. The #
> of records I'm working with is small (3,000-10,000). If I perform a
> "test, the rows are actually inserted, and rather quickly. If I
> execute the package through normal methods, the execution takes several
> hours and often times out?
> Any ideas'
>
There's really not enough info to come up with a cause, but if this is a
SQL-to-SQL process (reading from SQL/writing to SQL), I'd question why
you used DTS to do this. I think you'd get better performance, not to
mention easier debugging, by doing this a series of INSERT INTO/SELECT
statements.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

DTS Job ActiveX Script Help

I have a DTS Package Job that needs to pre-check a txt file (see below) with a 'Date' in it. TO compare it with the current Date (execution Date -> today). If they match, move on to the next step and fail otherwise. I don't know how to create an ActiveX script to do this kind of comparison.

----------------------
Volume Unit Referred SBR Used Recfm SSNE BlkSz Dsorg Dsname
5GSL4B 6760 2005/03/09 1065535 FB 3000 27000 PS 'AAS3P.QT.SECMRK.ZXWSDB.FULL.UNPACKED'
----------------------

Thank you for any suggestion!

J827I would create the text file as another data source. You can then grab the first line, and put the date into the global variable or something, then run an activeX script to compare the global variable to the current date.

Does that kinda achieve what you are after?

DTS Help

Hey guys,
In my DTS package I have an ActiveX Script that basicaly checkes if records exist in a table something like this:
Const adOpenKeyset = 1
Const adLockOptimistic = 3

Function FindRec()

Dim objConn
Dim sql
Dim rs

Dim Success

Success = 0

sqlconn = "DSN=SAConn;UID=PSI_Admin;PWD=psiadmin001;DATABASE= SA"

Set objConn = CreateObject("ADODB.Connection")
objConn.Open sqlconn

sql = "SELECT NAM FROM tblUnassignedNAM "

Set rs = CreateObject("ADODB.RecordSet")
rs.Open sql, objConn, adOpenKeyset, adLockOptimistic

If rs.EOF = False Then

Success = 1

End If

rs.Close
Set rs = Nothing

objConn.Close
Set objConn = Nothing

If Success = 1 Then
FindRec = DTSStepExecResult_Success
ElseIf Success = 0 Then
FindRec = DTSStepExecResult_Failure
End If

End Function

If you notice at the end I say
If Success = 1 Then
FindRec = DTSStepExecResult_Success
ElseIf Success = 0 Then
FindRec = DTSStepExecResult_Failure
End If

So if there are no records it should fail and do something else, but the process does not fail it's successful. Why is that?You are simply returning the numeric value of the errors. You are not throwing an error.