In our previous article of this series, we presented a way of creating DTS
packages with wizards. Despite its simplicity, this approach has some
limitations, which you can overcome in two ways – by using DTS Designer or by
manipulating DTS object model with programming languages, such as Visual Basic.
While the second of these methods gives you access to essentially every DTS
feature, the first one is certainly more popular, due to its combination of
relative ease of use and rich functionality. These are also the reasons for
which we will concentrate on DTS Designer throughout our series.
DTS Designer, accessible from Data Transformation Services node in SQL
Server Enterprise Manager, provides the ability to create and edit DTS packages
via a graphical interface. A package consists of several different components
such as connections, tasks, and workflow logic, identifiable by unique icons,
which together form visual representation of the data flow and transformations.
(You can take a quick look at how a sample package is structured by opening one
created with the Export/Import wizard, we described in our previous
article, providing, of course, that you have saved it first). We will start
by describing in more details the first one of these components – connections.
Connections constitute an essential part of any package, since they provide
access to the source and destination of data. DTS implements connections by
employing OLE DB technology, which, in addition to performance benefits, also offers
extensibility allowing use of custom OLE DB providers to connect to
non-standard data stores. In general, connection types can be grouped in the
allowing direct access via OLE DB providers to standard databases
such as Microsoft SQL Server 2000, Microsoft Access 2000, dbase 5, Paradox 5.x,
allowing indirect access via OLE DB provider for ODBC drivers to
any ODBC-based connections (this includes Oracle),
allowing access to non-database data sources such as Microsoft
Excel 97 and 2000, HTML files, and text files in practically any format type
(delimited and fixed field, ASCII and UNICODE, etc.).
Every one of these connection types is represented by a separate icon in the
DTS Designer interface in the Connections section of the window. Properties of
each type of connection depend on the OLE DB provider. For example, when
creating a SQL 2000 Server connection, you will be prompted for the name of a
server instance, type of authentication (Windows or SQL Server), username and
password (providing you selected SQL Server Authentication), and target
database. You will also have a chance to set a number of advanced properties
(by clicking on the Advanced command button), such as "Persist Security
Info" (which we will discuss later in this article), Application Name, or
Workstation ID (which correspond to hostname and program_name columns in master.dbo.sysprocesses
and are useful when monitoring or troubleshooting processes running on the
server, e.g. with sp_who2 stored procedure). In every case, you need to provide
a unique (and preferably descriptive) connection name.
There is also another way of categorizing connection types, which is based
on the way connection information is stored. If you add any of the above-described
connections to your package using their corresponding icons, they will become
an integral part of this package. While this makes connection parameters (such
as login and the password needed to connect to the data store) as secure as the
package itself, the connection information remains static, even after the
package is copied to a different location. To avoid the need for modifying the
package to adjust connection properties, you can use external Microsoft Data
Link files, which provide an alternate way of storing connection information.
UDL files simplify dealing with packages that are copied and executed against
different data locations. Since connection parameters are stored in a separate
file, the content of the package does not need to be altered in any way.
Instead, all you have to do is modify new connection properties stored in the
UDL file. Alternatively, you can also use global variables, Dynamic Properties
task, or write an ActiveX script that modifies connection properties using the
DTS object model (we will cover these methods in details in our future
Configuring a DTS package with an external UDL file involves two distinct
Creation of a UDL file (outside of SQL Server 2000 Enterprise
Manager) – the simplest way to create a UDL file is by using Windows Explorer.
In the Explorer window, select the folder where you want to store the file, so
its content is displayed in the details pane. Display context-sensitive menu by
clicking on the empty area within the folder content window and select New
-> Text Document option (alternatively, you can choose the same option from
the File menu). Specify the name of the file and ensure that its extension is
changed from the default .TXT to .UDL. In order for this to work properly, you
need to ensure that "Hide extensions for known file types" option
from the View tab of Folder Options (in the Explorer Tools menu) is cleared.
Once the extension is properly assigned, you should automatically see that the
icon of the file properly reflects its function (it looks the same as Microsoft
Data Link in the DTS Designer window). At this point, you can simply
double-click on it (or select the Open option from the context sensitive menu
of the UDL file). This will display the Data Link Properties dialog box,
divided into four tabs:
Provider – allowing you to select an OLE DB provider used by the
connection. For example, selecting Microsoft OLE DB Provider for ODBC Drivers
will give you access to all Data Source Names defined in ODBC Data Source
Administrators. The number of providers listed will depend on which ones are
installed on the system where the UDL file is created.
Connection – containing values determining connection parameters,
which are OLE DB provider specific, which means that what appears on this tab
depends on the choice you made on the Provider tab. In general, this would
include the location of the data store and authentication options. You also
have an option of verifying validity of the connection information you
specified by clicking on the Test Connection button.
Advanced – determines Impersonation and Protection level,
connection timeout, and access permissions
All – gives you a comprehensive listing of properties available
on the remaining three tabs, in addition to advanced connection properties
(such as mentioned before "Persist Security Info," "Workstation
ID," or "Application Name," when using SQL Server OLE DB
Note that UDL is a text file, so all of its
information can be easily read, which has obvious security implications, which
we will discuss shortly.
Referencing the UDL file created in the first step within SQL
Server Enterprise Manager – this is done from the DTS Designer interface by
clicking on the Microsoft Data Link icon in the Connection area of the window.
This brings up the Connection Properties dialog box, from which you need to
specify a new connection name and either select an existing UDL file or provide
new connection information by clicking on Properties command button (which
displays the same Data Link Properties dialog box as the one presented when
creating UDL files with Windows Explorer, described in the previous section).
In order to accomplish our goal and simplify portability of the package, you
need to choose the first option and use the Browse button to locate the
previously created UDL file. Note that the second option clearly indicates that
the UDL information can also be embedded in the package (if you select it, you
will be presented with the same Data Link Properties dialog box that was
displayed after double-clicking on the UDL file in Windows Explorer window). In
fact, you can even cache information contained in an external UDL file in the
package. This behavior is controlled with a single checkbox labeled "Always
read properties from UDL file." If the checkbox is cleared, the connection
parameters from the external UDL file are cached at the design time and if you
want to modify them, then they must be edited directly from DTS Designer (or programmatically).
Otherwise, when the checkbox is selected, the UDL file is always referenced
when a connection is established (which is likely what you want to accomplish).
When using UDL files, one of important security considerations is the fact
that they store all of their information (including passwords) in unencrypted,
clear text format. In order to work around this issue, you can use Windows
Authentication, which does not require login information to be included, but
instead it uses credentials of the user who is logged on when the package is
executed (or SQL Server Agent account in case a package is scheduled as a SQL
Server job). Another security consideration, which applies to any securable connection,
is the fact that by default, authentication information used to connect to a
data store is cached within the package. You can control this behavior by
modifying the value of the Persist Security Info property, accessible from the Advanced
Connection Properties dialog box (you access it by selecting the Properties
option from the connection context sensitive menu and clicking on the Advanced
button on the General tab of the connection Properties dialog box). This is a boolean
value, where 1 enables caching security information and 0 disables it.
When editing a connection property, DTS Designer, by default, attempts to
verify its availability. Since this might not be desired in some cases (for
example, whenever the data store is not directly available during the design
stage), you can apply the Disconnected Edit option, accessible from the Package
menu. Selecting it displays the Edit All Package Properties dialog box, from
which, besides connections, you can also modify tasks, steps and global
variables, without automatic verification.
Other consideration when configuring connections in DTS packages is
parallelism. DTS actions (tasks) accessing the same data store can execute
simultaneously, but in order for this to happen you need to ensure that all of
them use separate connection objects. Note that this does not mean that you
have to use separate external UDL files – a single UDL file will suffice as
long as there are multiple Microsoft Data Link connections within a DTS package
(since the UDL file simply contains the connection information, but does not
represent a DTS connection object).
In the next article, we will start an overview of DTS tasks – the component
type responsible for data transformation.