Using Data Link Connection Objects

In the packages you created in previous chapters, each connection object was defined within each package in the data movement application. Embedding connection information in each package, however, makes it difficult to migrate the data movement application ”a process that involves changing data sources and destinations ”without opening and editing each connection object in each package. Opening and manually editing packages is time consuming and error prone.

In Microsoft SQL Server 2000, you can solve this migration problem by configuring each connection object to read a Universal Data Link (UDL) file at run time and retrieve connection information. This retrieval process is called a data link connection . A UDL file is an external file that you can configure to access an OLE DB data source using any of the system-installed OLE DB providers. A UDL file, which is configured to connect to an OLE DB data source, is similar to a file Data Source Name (DSN), which is configured to connect to an ODBC data source. The connection information in the UDL file is not resolved until run time, and each UDL file can be edited independently of the DTS packages that refer to it. A single UDL file can be used by multiple packages within the data movement application without affecting parallelism.

Implementing a data link connection involves creating a UDL file in Microsoft Windows Explorer, storing connection information in the UDL file, and then configuring a connection object to use the UDL file. In the following procedures, you will create two UDL files in Windows Explorer, store information about how to connect to the SBS_OLTP and SBS_OLAP databases in these UDL files, and then configure the connections in the UpdateCustomerDim , UpdateProductDim , PopulateTimeDimension , and LoadHistoricalData packages to use these UDL files.

Note  

If you skipped Chapter 5, execute the IfYouSkippedChapter5.cmd batch file in the C:\Microsoft Press\SQL DTS SBS\Ch6\SkippedChapterFiles folder. This batch file restores the SBS_OLTP and SBS_OLAP databases and copies the DTS packages that would have been created in Chapters 1 through 5 into the appropriate folders. If you do not want this batch file to overwrite any packages that you created in Chapters 1 through 5, you must move them or rename them before you execute this batch file.

Create UDL files

1. Click Start and then click Run.

2. In the Open box, type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication , and then click OK.

3. On the Tools menu, click Folder Options.

4. Click View, clear the Hide Extensions For Known File Types check box, and then click OK.

5. Right-click an open area in the Windows Explorer window, point to New, and then click Text Document.

6. Type SBS_OLTP.udl , click Enter, and then click Yes.

   Until you configure a UDL file, the file is simply a blank text file with the .udl extension.

7. Right-click an open area in the Windows Explorer window, point to New, and then click Text Document.

8. Type SBS_OLAP.udl , click Enter, and then click Yes.

You have successfully created two UDL files. Next you need to configure these files.

Configure UDL files

1. In Windows Explorer, double-click SBS_OLTP.udl.

   Windows recognizes the .udl extension and opens a Data Link Properties dialog box to enable you to configure the properties of this UDL file.

   

   Click the Provider tab, which lists the different providers from which you can choose, and select the Microsoft OLE DB Provider For SQL Server.

   

2. Click Next, type (local) , click Use Windows NT Integrated Security, and then select SBS_OLTP in the Select The Database On The Server list.

   Tip  

   This UDL file will always try to connect to the default instance on the local server. If you later want to configure this UDL file to connect to a named instance on the local server or to an instance on a remote server, you can change this UDL file to point to a specific SQL Server instance without directly editing the packages that use the file.

   

3. Click Test Connection, click OK, and then click OK again to save your changes. This completes the configuration of the SBS_OLTP.udl file.

4. Double-click SBS_OLAP.udl.

5. Click the Provider tab, and then click Microsoft OLE DB Provider For SQL Server.

6. Click Next, type (local) , click Use Windows NT Integrated Security, and then select SBS_OLAP in the Select The Database On The Server list.

7. Click Test Connection, click OK, and then click OK again to save your changes.

You have successfully configured two UDL files. Now you are ready to use these files in your existing packages.

Configure the connections in the UpdateProductDim package to use a UDL file

1. Open Microsoft SQL Server Enterprise Manager.

2. In the SQL Server Enterprise Manager console tree, expand your local SQL Server instance, right-click Data Transformation Services, and then click Open Package.

3. Navigate to C:\Microsoft Press\SQL DTS SBS\DataMovementApplication in the Look In list, and then double-click UpdateProductDim.dts.

4. Double-click UpdateProductDim in the Select Package dialog box, type mypassword in the Password text box, and then click OK.

5. Double-click the ProductStage connection object to change the data source for the ProductStage connection object to the SBS_OLAP.udl file that you previously configured.

   

6. Select Microsoft Data Link in the Data Source list, and then type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\SBS_OLAP.udl in the UDL File Name box.

   Tip  

   If you click Properties in this dialog box after entering a valid UDL file and path , you can edit the properties of the UDL file from within DTS.

7. Select the Always Read Properties From UDL File check box.

   Important  

   If you do not select the Always Read Properties From UDL File check box, DTS will copy the connection string into the package and will not update it in the package when you make changes to the UDL file.

   

8. Click OK to save the modified connection object, and then click OK to keep all existing transformations that reference this connection object.

   You have successfully modified the ProductStage connection object to use the SBS_OLAP.udl file. Notice that the icon representing the connection object on the design sheet has changed.

9. Double-click the SBS_OLAPAdditionalConnection connection object to change the data source for the SBS_OLAPAdditionalConnection connection object to the SBS_OLAP.udl file that you previously configured.

10. Select Microsoft Data Link in the Data Source list, type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\SBS_OLAP.udl in the UDL File Name box, select the Always Read Properties From UDL File check box, and then click OK to save the modified connection object.

11. Click OK to keep all existing transformations that reference this connection object. Since you are not actually changing the data source, you do not need to modify any transformation in the package.

12. On the toolbar, click Execute to verify the connection objects in the package function properly.

13. Click OK and then click Done.

    Tip  

    If you save your package each time you make a few changes, the size of the structured storage file can get very large by the time you finish modifying your packages because each version of the package is saved in the structured storage file. However, if you delete underlying structured storage file while a package is open and then save the open package, only the most recent version is saved and the package GUID is not changed.

You have successfully modified all connections in the UpdateProductDim package to allow them to be dynamically configured.

Configure the connections in the UpdateCustomerDim package to use a UDL file

1. Switch to the SQL Server Enterprise Manager console tree, right- click Data Transformation Services in your local instance, and then click Open Package.

2. Navigate to C:\Microsoft Press\SQL DTS SBS\DataMovementApplication in the Look In list, and then double-click UpdateCustomerDim.dts.

3. Double-click UpdateCustomerDim in the Select Package dialog box, type mypassword in the Password text box, and then click OK.

4. Double-click the CustomerStage connection object to change the data source for the CustomerStage connection object to the SBS_OLAP.udl file that you previously configured.

   Note  

   When multiple connection objects use the same UDL file, parallelism is not affected. The UDL file contains only the connection information; it does not make the actual connection.

5. Select Microsoft Data Link in the Data Source list, type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\SBS_OLAP.udl in the UDL File Name box, select the Always Read Properties From UDL File check box, and then click OK to save the modified connection object.

6. Click OK to keep all existing transformations that reference this connection object.

7. Double-click the SBS_OLAPAdditionalConnection connection object to change the data source for the SBS_OLAPAdditionalConnection connection object to the SBS_OLAP.udl file that you previously configured.

8. Select Microsoft Data Link in the Data Source list, type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\SBS_OLAP.udl in the UDL File Name box, select the Always Read Properties From UDL File check box, and then click OK to save the modified connection object.

9. On the toolbar, click Execute to verify that the connection objects in the package function properly.

   Even though the connection object functions properly, the Load CustomerStage Table step still fails because the number of errors in the source file exceeds the number of errors permitted by the Load CustomerStage Table step.

10. Click OK and then click Done.

You have successfully modified each of the connections in the UpdateCustomerDim package to allow them to be dynamically configured.

Configure the connection in the PopulateTimeDimension package to use a UDL file

1. Switch to the SQL Server Enterprise Manager console tree, right- click Data Transformation Services in your local instance, and then click Open Package.

2. Navigate to C:\Microsoft Press\SQL DTS SBS\DataMovementApplication in the Look In list, and then double-click PopulateTimeDimension.dts.

3. Double-click PopulateTimeDimension in the Select Package dialog box, type mypassword in the Password text box, and then click OK.

4. Double-click the SBS_OLAPDestination connection object to change the data source for the SBS_OLAPDestination connection object to the SBS_OLAP.udl file that you previously configured.

5. Select Microsoft Data Link in the Data Source list, type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\SBS_OLAP.udl in the UDL File Name box, select the Always Read Properties From UDL File check box, and then click OK to save the modified connection object.

6. On the toolbar, click Save and then close the PopulateTimeDimension package in DTS Designer.

You have successfully modified the connection in the PopulateTimeDimension package to allow it to be dynamically configured.

Configure the LoadHistoricalData package to use the UDL files

1. Switch to the SQL Server Enterprise Manager console tree, right- click Data Transformation Services in your local instance, and then click Open Package.

2. Navigate to C:\Microsoft Press\SQL DTS SBS\DataMovementApplication in the Look In list, and then double-click LoadHistoricalData.dts.

3. Double-click LoadHistoricalData in the Select Package dialog box, type mypassword in the Password text box, and then click OK.

4. Double-click the ProductsSource connection object to change its data source to the SBS_OLTP.udl file that you previously configured.

5. Select Microsoft Data Link in the Data Source list, type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\SBS_OLTP.udl in the UDL File Name box, select the Always Read Properties From UDL File check box, and then click OK to save the modified connection object.

6. Click OK to keep all existing transformations that reference this connection object.

7. Double-click the CustomersSource connection object to change its data source to the SBS_OLTP.udl file that you previously configured.

8. Select Microsoft Data Link in the Data Source list, type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\SBS_OLTP.udl in the UDL File Name box, select the Always Read Properties From UDL File check box, and then click OK to save the modified connection object.

9. Click OK to keep all existing transformations that reference this connection object.

10. Double-click the ProductsDestination connection object to change its data source to the SBS_OLAP.udl file that you previously configured.

11. Select Microsoft Data Link in the Data Source list, type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\SBS_OLAP.udl in the UDL File Name box, select the Always Read Properties From UDL File check box, and then click OK to save the modified connection object.

12. Click OK to keep all existing transformations that reference this connection object.

13. Double-click the CustomersDestination connection object to change its data source to the SBS_OLAP.udl file that you previously configured.

14. Select Microsoft Data Link in the Data Source list, type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\SBS_OLAP.udl in the UDL File Name box, select the Always Read Properties From UDL File check box, and then click OK to save the modified connection object.

15. Click OK to keep all existing transformations that reference this connection object.

16. Double-click the 1996DataDestination connection object to change its data source to the SBS_OLAP.udl file that you previously configured.

17. Select Microsoft Data Link in the Data Source list, type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\SBS_OLAP.udl in the UDL File Name box, select the Always Read Properties From UDL File check box, and then click OK to save the modified connection object.

    Notice that you are not prompted to update transformations because this connection is used by a Bulk Insert task rather than a Transform Data task. There are no transformations to be updated.

18. Double-click the 1997DataDestination connection object to change its data source to the SBS_OLAP.udl file that you previously configured.

19. Select Microsoft Data Link in the Data Source list, type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\SBS_OLAP.udl in the UDL File Name box, select the Always Read Properties From UDL File check box, and then click OK to save the modified connection object.

20. Double-click the SBS_OLAPAdditionalConnection connection object to change its data source to the SBS_OLAP.udl file that you previously configured.

21. Select Microsoft Data Link in the Data Source list, type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\SBS_OLAP.udl in the UDL File Name box, select the Always Read Properties From UDL File check box, and then click OK to save the modified connection object.

22. Click OK to keep all existing transformations that reference this connection object.

23. Double-click the SalesFactDataDestination connection object to change its data source to the SBS_OLAP.udl file that you previously configured.

24. Select Microsoft Data Link in the Data Source list, type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\SBS_OLAP.udl in the UDL File Name box, select the Always Read Properties From UDL File check box, and then click OK to save the modified connection object.

25. Click OK to keep all existing transformations that reference this connection object.

26. On the toolbar, click Execute to verify that the connection objects in the package function properly.

    Tip  

    It is a good idea to open each connection object to verify that the Always Read Properties From UDL File check box is selected. Verifying that the option is selected is easy to forget, but you will not see an error until you change the connection information in the UDL file and execute the package. Unless this check box is selected, the package will not use the updated information in the UDL file.

27. Click OK and then click Done.

You have successfully configured each connection object in each of these packages to use UDL files. As you can see, even when only four packages are involved in a data movement application, opening and modifying each connection object in each package every time connection information changes is a tedious process. Now that your connection objects are using UDL files, merely editing these two UDL files enables the tasks in each package to access different data sources and destinations.

However, your data movement application is still dependent on hard-coded paths in the packages that specify the names and locations of these UDL files. Furthermore, these packages still contain hard-coded paths to identify the names and locations of the delimited text files used in the Bulk Insert tasks. In the next section, you will learn how to use the Dynamic Properties task and an initialization file to enable these paths to be set dynamically at run time.