Hello,

We recently upgraded from SQL2k to SQL2005 and our DTS packages, we have two, run fine when I run them right from the Legacy folder created when we converted them after the upgrade but fail when they run as a job. This is the error: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. Error source: Microsoft OLE DB Provider for SQL Server

It gets an error when it gets moved to a staging area, here is the vbscript

Dim o ''As Scripting.FileSystemObject
Dim f ''As Scripting.File
Dim destinationFolder ''As Scripting.Folder
Dim t ''As Scripting.TextStream

Const LS_DESTINATION_FILENAME = "\\servername\d$\Inetpub\wwwroot\ourfolder\webdir\document.txt"
Const LS_SOURCE_FILENAME = "\\servername\data\InfoService\ASSOC\WebDir\Test\document.txt"

Set o = CreateObject("Scripting.FileSystemObject")

o.CopyFile LS_SOURCE_FILENAME, LS_DESTINATION_FILENAME, True

Set destinationFolder = o.GetFolder("\\servername\d$\Inetpub\wwwroot\ourfolder\webdir")
Set t = o.OpenTextFile("C:\myfile.txt", 8, -1)

For Each f In destinationFolder.Files
t.WriteLine f.Name
Next

SET o=nothing
SET f = Nothing
SET d = Nothing
SET t = Nothing


Thanks!

The error you get isn't coming from this piece of script, once you've moved the files to the destination folder what's the next step?

I would guess thats where somethign actually refers to the SQL server.

The messhge you get is beacuse of the ODBC you use to connect to the server, this exists on your machine but whne the package is run as a job the ODBC won't exist on the server.

Have a look at the next step, find the ODBC connection then create it on the server.

I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

Thanks Doug. Working on it now. Will keep you posted.

Smatthews

I added the ODBC connections that I was missing on the server from my workstation but I still get the same error.

They run on my workstation and the server when I run them from the Legacy folder but they won't run from either place when I run them through SQL Server Agent as a job.

Any other suggestions?

Thanks.

Smatthews

I have a hunch, but could be wrong, so...

Can you check something for me on your server?

Open the services control panel, scroll down to the SQL Server service. Right Click -> Properties, click "Log On" tab. What do you have it configured for?

Do the same thing for the SQL Server Agent service.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

Both are configured for This Account with the NetworkName\Administrator as the user.