SQL Server 2000 DTS Part 3 - DTS Designer Connections
November 11, 2003
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 following categories:
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 articles).
Configuring a DTS package with an external UDL file involves two distinct steps:
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.