I have some SQL Server 7.0 DTS packages that use Microsoft data Link files
(*.udl) to define the DataSource for DataTransfer/pump tasks. The source DB
are Sybase ASE11 Databases.

The UDLs are defined like.
[oledb]
; Everything after this line is an OLE DB initstring
Provider=MSDASQL.1;Password=pass;Persist Security Info=True;User
ID=userName;Extended Properties="DRIVER={MERANT 3.60 32-BIT
Sybase};UID=userName;DB=testDB;SRVR=DSRV0002;PWD=p ass";Initial
Catalog=testDataBase

Despite ticking "Always read properties from UDL file", the DTS packages
seem to always pick up the connection details to when the DTS package was
last saved (i.e., if the UDL files are updated to point to a new database
host, the DTS package still try to connect to the old datasource UNTIL the
DTS package is re-opened in DTS designed under SQL Server Enterprise Manager,
and resaved).

How could I fix this??

I suspect the UDL is grabbed and read at package load time, not execute. Not
a problem for production, but you may hit this if desiging and changing the
Udl with the package still open.

Darren Green
http://www.sqldts.com
http://www.sqlis.com

"Patrick" <questions (AT) newsgroup (DOT) nospam> wrote

Ok, cancel that it is rubbish. It does read the file every time you execute
the package, even whilst working in the designer, without closing stuff down
in between. You can prove it reads the file by using FileMon
(www.sysinternals.com). I also tested with a simple package using a SQL
Server OLE-DB based UDL, and if I changed the UDL by hand (notepad) outside
of DTS, with my package still open and re-executed it picked up the changes
just fine.

I cannot see your problem. Only other issue I know of is some tasks include
the database name, as part of the "object" name. See the DataPump
SourceObjectName and DestinationObjectName properties.

Darren Green
http://www.sqldts.com
http://www.sqlis.com


"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote

My DataPump Source is a SQL statement like
exec_dataExtract param1,param2
As opposed to
oldDatabaseServer.Schema.Table

So, that should rule out this problem, right?

Btw, when you say it works, was it with SQL Server 7.0 or SQL Server 2000?

We are using SQL Server 7.0 (still), though upgrading soon.


"Darren Green" wrote:

From Books Online SQL Server 2000. Note if you use the 7.0 tools, there is
no option to check read from file, as it did not exist.

Data Link Connection
In Microsoft® SQL ServerT 2000, Data Transformation Services (DTS) packages
can use Microsoft Data Link (.udl) files to create OLE DB connections and
resolve the connections at run time. This feature lets you encapsulate the
connection properties from a DTS package into a separate file. In situations
where connection information such as the server name, login, or even the OLE
DB provider may change, you can edit the connection string in a data link
file instead of the connection properties in a DTS package.

In Microsoft SQL Server version 7.0, you can use a data link file, but the
connections are not resolved at run time and can be modified only by editing
the DTS package. This choice is useful in situations where an OLE DB
provider has special connection requirements that can be addressed only
through the data link dialog boxes.

If you are specifying a data link connection, you first must specify whether
to load an existing data link file or create a data link to save with DTS
Designer.

If you want to use a data link file rather than saving the data link with
DTS Designer, you can create one either from Windows Explorer or during the
data link configuration process in DTS Designer.


Darren Green
http://www.sqldts.com
http://www.sqlis.com

"Patrick" <questions (AT) newsgroup (DOT) nospam> wrote

I designed the DTS package using the Enterprise Manager version 8 (SQL Server
2000??) to design the DTS package, which may explain why the DTS packages are
not reading the UDL files at run time???

As I am upgrading to SQL Server 2000 soon, would I need further actions to
get the DTS to read from the UDL file??

"Darren Green" wrote: