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:

• 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 articles).

Configuring a DTS package with an external UDL file involves two distinct steps:

• 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 Provider).

  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.

» See All Articles by Columnist Marcin Policht