I have created a small ActiveX script which can load multiple DTS packages
from the File System up to a SQL 2000 Server. The script works just great
when executed as an ActiveX script in a DTS package like so;

Dim fso, f, f1, fc
Dim s
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder("C:\Temp\DB_Build\DTS")
Set fc = f.Files
For Each f1 in fc
Dim MyDTS
Set MyDTS = CreateObject("DTS.Package")
s = "C:\Temp\DB_Build\DTS\" & CStr( f1.Name )
MyDTS.LoadFromStorageFile s,""
MyDTS.SaveToSQLServer
"(local)",,,DTSSQLStgFlag_UseTrustedConnection,,,, ,True
Next

set fso = nothing
set f = nothing
set f1 = nothing
set fc = nothing
set MyDTS = nothing
set s = nothing

I would like to be able to run this Script directly in a SQL job but when I
do I get the following error message;

Executed as user: xx\xxx. Error Code: 0 Error Source= Microsoft OLE DB
Provider for SQL Server Error Description: Invalid authorization
specification Error on Line 10. The step failed.

It seems to "not like" the LoadFromStorageFile instruction when saved in a
job but again the exact code works just fine when run within a DTS.

My question, if anyone knows, is what is the difference when running an
ActiveX script within a DTS versus running the script directly in a job? I'm
trying to figure out if I am wasting my time trying to do it this way. Any
help is appreciated. Thanks.

-RJ

When you execute the package, are you logged in as the same user under which
the job is executing? If not, have you tried to log in as the xx\xxx user
(from the job error) and run the DTS package?
--
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
--
"RJ" <rjbook (AT) newsgroup (DOT) nospam> wrote

Hello Peter,

I would not expect permissions to be an issue since the xx\xxx is the Domain
Account that SQL Agent runs under and it is a Local Admin on the box. Just as
a test though I logged on to my machine as the xx\xxx User and was able to
run the ActiveX script in a DTS package same as when I was using my Windows
Account.

As another test I saved the script as a .vbs file and tried running it, I
get the same error message as when the script is run from the job, since I
can't paste the screen shot here is the error from the dialog box;

Windows Script Host
Script: C:\Temp\DB_Build\Load_DTS_PAckages.vbs
Line: 11
Char: 8
Error: Invalid authourization specification
Code: 8004E4D
Source: Microsoft OLE DB Provider for SQL Server

So again it would seem that the ActiveX Script Task within the DTS package
is providing some sort of "support" or "default authorization" that is not
available when running the script directly. I am trying to figure out what
that would be since I need to be able to load DTS packages from either a SQL
Job or directly from Script.

Ultimately what I am trying to do is fully automate the Deployment of SQL
2000 databases including DTS packages. I have come up with a "home grown"
solution for the databases but installing the DTS packages is still an issue.
I have about 2 dozen packages I need to deploy and I have used the 3rd party
tool DTSBackup 2000 which works well but it is still a manual process of
selecting and moving the .dts files.

Does anyone have a workable solution that can deploy multiple DTS packages
as part of an Installer package or Custom script? The other requirement is
the DTS packages may already exist on the Target Server so I need to be able
to drop them before reloading from Script from the local file system. Thanks.

-RJ





"Peter W. DeBetta" wrote:

Ah, I figured it out! I had to reproduce it here to see what was really
going on.

The flags argument constant DTSSQLStgFlag_UseTrustedConnection (256) is not
known outside the dev environment, so it's using a default value of zero (0)
for the flags argument which is causing the login to be attempted using SQL
Authentication, which of course fails since there are no credentials.

So replace this...

MyDTS.SaveToSQLServer
"(local)",,,DTSSQLStgFlag_UseTrustedConnection,,,, ,True

with this...

MyDTS.SaveToSQLServer "(local)",,,256,,,,,True

and it should work.

--
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
--
"RJ" <rjbook (AT) newsgroup (DOT) nospam> wrote

Oh man, I would have never figured that out (especially since I was looking
in the wrong spot). Thanks Peter! I have tested your fix and it it working,
now I am back in business. :-)

-RJ

"Peter W. DeBetta" wrote: