I've got several packages running on a server that copy tables from an ODBC
compliant data source to SQL Server. When executing the packages via
Enterprise Manager on my local machine, they run fine, using the DSN's setup
on my machine. However, when the packages are scheduled and run in the
middle of the night (I assumed from the server where they resided), certain
tables where not found in the source database.

I could tell by the missing tables that the package was using an older
version of a catalog referenced by the DSN, so I thought the DSN just needed
to be updated on the server. But lo and behold when I went to update the
DSN, there weren't any! Not even old ones!

So where the heck are the DTS packages getting the information to connect to
the datasource?

I'm so confused...

Thanks,
JT

You can find out what your DSN'd connections think they are usinf by looking
in

Disconencted Edit

Connections | Choose a connection | OLE DB Properties | Extended Properties

for one of mine I have

DSN=AS400;UID=GENIO;PWD=HowNowBrownCow;

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"JT Autry" <Just ask> wrote

Allan, thanks for the reply. The values show pretty much what I expected.
Are these values stored with the package? Or do they depend on the DSN on
the machine on which the package executes?


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

DTS will look for a DSN with that name on the server the packageruns on. If
you move the package and the DSN does not exist it will fail

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"JT Autry" <Just ask> wrote

It all makes sense now. I didn't not notice there was a system DSN that was
pointing to an older version of our catalog.

I still think it should use the server's data sources rather than the
client's when executing via enterprise manager. Oh well!

Thanks for your help.

JT

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

No. DTS is client side so will use the clients perspective and resources.

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"JT Autry" <Just ask> wrote