Saving Packages

You saved the packages that you created in Chapter 1 and Chapter 2 to the Microsoft Windows operating system as structured storage files. As you will see, saving packages to the file system increases the portability of the packages and enables you to have different versions of packages with identical names in the same system. You can save a DTS package in one of the following four package formats:

• SQL Server Packages saved to SQL Server are saved to the sysdtspackages table in the msdb database in an instance of SQL Server. Packages saved to SQL Server are faster to load and save than packages saved to Meta Data Services, but not as fast to load or save as packages saved to a structured storage file. Saving large packages or a large number of packages to SQL Server can cause the msdb database to become quite large.

• Meta Data Services Packages saved to Meta Data Services are saved to a repository stored in the msdb database. The Meta Data Services repository uses the Open Information Model (OIM) and the Universal Modeling Language (UML). Meta Data Services stores package meta data and execution information, database schemas, data transformation details, data analysis descriptions, and COM objects. The package execution information stored can include data lineage information; this lineage information enables you to track the original source of each row of transformed data and provides an execution audit trail. However, these additional features make packages stored in Meta Data Services slower to save, load, and execute than packages stored in SQL Server or in a structured storage file. Saving packages to Meta Data Services can also cause the msdb database to become quite large ”even larger than packages stored to SQL Server.

• Structured storage file Packages saved as structured storage files are saved to the file system as .dts files using COM-structured storage. Packages stored in the file system are faster to save and load than packages stored in SQL Server or Meta Data Services. Packages saved as structured storage files are easier to move, both individually and as a group , across the network than packages saved to SQL Server or Meta Data Services. Saving packages to the file system makes it easy to migrate an entire data movement application from the development computer to a production computer by simply copying all the .dts files. To move packages saved to SQL Server or Meta Data Services, you must open them individually and then save them to another location. In addition, when you store packages as structured storage files, you can use a folder structure to save different packages with identical names, enabling you to easily maintain different generations of the same data movement application on the same computer. Packages saved in the msdb database must have unique names due to the flat name space used by SQL Server.

• Microsoft Visual Basic file Packages saved as Visual Basic files are saved to the file system as .bas files for editing in Visual Basic and the Microsoft Visual Studio development environment. Once a package has been saved as a Visual Basic file, the package cannot be edited or executed through SQL Server. Saving packages as Visual Basic files is particularly useful for learning the syntax of the DTS objects that can be programmed.

When you save a package, DTS assigns a Globally Unique Identifier (GUID) to identify it. When you save a package to SQL Server, Meta Data Services, or a structured storage file, DTS employs a form of version control to assist the developer as changes are made to a package. Each time a package is saved, a GUID is assigned to the version being saved, and each version is saved within the same package. Saving multiple versions of each package enables you to load an older version in case you need to roll a package back to an earlier version because of a programming error, or because you need to determine how a package changed over time. However, this save feature can cause a package to eventually become quite large; packages saved to SQL Server or Meta Data Services can also cause the msdb database to become quite large.

Important  

If you save a package to SQL Server, you can directly delete older versions of it when these versions become obsolete. If you save a package to Meta Data Services or a structured storage file, the only way to delete older versions within the package is to save the updated package as a new package and then delete the old package and rename the new one to the original name.

Note  

If you skipped Chapter 2, execute the IfYouSkippedChapter2.cmd batch file. This batch file restores the SBS_OLTP and SBS_OLAP databases and copies the DTS packages that would have been created in Chapters 1 and 2 into the appropriate folders. If you do not want this batch file to overwrite any packages that you created in Chapters 1 and 2, you must move them or rename them before you execute this batch file.

Work with version control in a structured storage file

In this procedure, you will learn how to load a particular version of a package stored in a structured storage file, and how to delete obsolete versions of a package to reduce the size of a structured storage file.

1. In Microsoft Windows, click the Start button, point to All Programs, point to Microsoft SQL Server, and then click Enterprise Manager.

2. In the SQL Server Enterprise Manager console tree, expand Microsoft SQL Servers, expand SQL Server Group, 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 LoadHistoricalData.dts.

   You are prompted to select a package to load from within this file. You can store multiple packages in a single structured storage file.

4. Expand LoadHistoricalData in the Select Package dialog box.

   This file contains three versions of the LoadHistoricalData package that are sorted by date, with the most recent version at the top of the list.

   

5. Double-click the second package in the list, type mypassword in the Password box, and then click OK.

   This version of the LoadHistoricalData package contains the connection objects but doesn t have any tasks or precedence constraints because they were not added at the time this version of the package was saved.

6. Close this package in DTS Designer, right-click Data Transformation Services in the SQL Server Enterprise Manager console tree, and click Open Package.

7. Double-click LoadHistoricalData.dts, double-click LoadHistoricalData in the Select Package dialog box, type mypassword in the Password box, and then click OK to open the most recent version of the LoadHistoricalData package in DTS Designer.

   Important  

   When you open or execute a package without specifying a particular version, the most recent version is always used. This feature enables you to create batch scripts that always execute the most recent version of a DTS package without having to edit the batch script.

8. On the Package menu, click Save As.

9. Type mypassword in the Owner Password box, change the file name to C:\Microsoft Press\SQL DTS SBS\Ch3\WorkingFolder\LoadHistoricalData.dts in the File Name box, and then click OK.

10. Type mypassword in the Password box, and then click OK. Click OK to acknowledge that the package cannot be executed without the owner password.

11. Close this package in DTS Designer, right-click Data Transformation Services in the SQL Server Enterprise Manager console tree, and click Open Package.

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

13. Expand LoadHistoricalData in the Select Package dialog box.

    Only one version of this package appears in the list. When you use Save As to save an existing package to the file system, only the most recent version of the package is saved in the new structured storage file.

    Tip  

    Saving an existing package that is stored as a structured storage file to a new location or using a new file name enables you to eliminate all previous versions of the package and reduce the size of a structured storage file.

14. Double-click LoadHistoricalData, type mypassword in the Password box, and then click OK to open the LoadHistoricalData package in DTS Designer.

Now that you have learned how to work with package versions stored in a structured storage file, let s look at how to work with package versions stored in SQL Server.

Work with version control in packages saved to SQL Server

In this procedure, you will learn how to load a particular version of a package stored in SQL Server and how to delete obsolete versions of a package.

1. On the Package menu, click Save As.

2. Type mypassword in the Owner Password box, select SQL Server in the Location list, ensure that (local) appears in the Server list, ensure that Windows Authentication is selected, and then click OK to save this package to your local SQL Server instance.

   

3. Type mypassword in the Confirm Package Owner Password dialog box, and then click OK.

4. Click OK to acknowledge that the package cannot be executed without the owner password.

5. On the design sheet, click Truncate Data and then press the Delete key.

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

7. In the SQL Server Enterprise Manager console tree, expand Data Transformation Services, and then click Local Packages.

   The LoadHistoricalData package that you saved appears in the details pane.

8. In the details pane, right-click LoadHistoricalData and then click Versions.

   Both the original version that you saved and the most recent version appear in the version list.

   

9. Click the most recent version of the package (the top one in the list) and then click Delete.

10. Click Edit to open the original version of the LoadHistoricalData package, type mypassword in the Password box, and then click OK to display it.

    Tip  

    When you save packages to SQL Server, the only way you can save two versions of a package with the same name is to use version control. You cannot save separately two identically named packages.

Now that you understand how to save a package to a structured storage file and to SQL Server, and use version control with each of these storage methods , let s briefly look at saving a package as a Visual Basic file. Then we ll discuss Meta Data Services.

Save a package as a Visual Basic file

In this procedure, you will save a package as a Visual Basic file and look at its contents.

1. On the Package menu, click Save As.

2. Select Visual Basic File in the Location list, type C:\Microsoft Press\SQL DTS SBS\Ch3\WorkingFolder\LoadHistoricalData.bas in the File Name box, and then click OK to save the LoadHistoricalData package as a Visual Basic file.

3. Open Notepad.

4. On the File menu, click Open.

5. In the Files Of Type list, select All Files.

6. Navigate to C:\Microsoft Press\SQL DTS SBS\Ch3\WorkingFolder, and then double-click LoadHistoricalData.bas.

   The Visual Basic file generated for LoadHistoricalData appears in Microsoft Notepad. You could extend or modify this package using your preferred development environment.

   

   Tip  

   Most developers use the Visual Basic file generated by DTS as example code only. The code generated by DTS is not necessarily modular, variables are not always initialized , and the generated transformations are not particularly effective. In addition, the Visual Basic generating tool in DTS Designer does not reverse engineer symbolic offsets; you must do this yourself to see the constant as well as the parameter.

7. Close Notepad.

Now that you have learned how to save packages to a structured storage file, SQL Server, and a Visual Basic file, you are ready to learn about the additional features available to you when you save your packages to Meta Data Services.

Save a package to Meta Data Services and link its meta data

In this procedure, you will learn how to save a package to Meta Data Services and link the package s meta data to meta data in Meta Data Services. Linking package meta data enables you to browse packages and package versions that use a specific column as a source or destination. It also lets you determine whether that specific column is used as a source or destination in any packages saved to Meta Data Services. This linking of columns to a package is called column-level data lineage .

Important  

Saving packages to Meta Data Services is an advanced option. By default, SQL Server Service Pack 3 (SP3) disables the option to store DTS packages to Meta Data Services because packages saved to Meta Data Services are not as secure as packages saved to SQL Server or to a structured storage file. Furthermore, Meta Data Services is a relatively new feature that is not used extensively in production environments due to performance problems with large systems.

1. On the Window menu in SQL Server Enterprise Manager, click Console Root\Microsoft SQL Servers\SQL Server Group\(local) (Windows NT)\DataTransformationServices\LocalPackages.

2. In the SQL Server Enterprise Manager console tree, right-click Data Transformation Services and then click Properties.

3. In the Package Properties dialog box, select the Enable Save To Meta Data Services check box and then click OK.

4. Click Yes to acknowledge that packages saved to Meta Data Services should not be considered secure.

5. On the SQL Server Enterprise Manager Window menu, click DTS Package: LoadHistoricalData.

6. On the Package menu, click Save As.

7. In the Location list, select Meta Data Services to display the scanning option.

   When you save a package to Meta Data Services, you cannot save the package using an owner or a user password. (For more information about securing packages saved to Meta Data Services, see the section titled Securing Packages Stored in Meta Data Services later in this chapter.)

   

8. Click Scanning.

   Scanning enables you to access the SQL Server 2000 OLE DB Scanner and specify how the data schema is recorded to Meta Data Services as meta data. By default, package meta data is not scanned to Meta Data Services.

   Tip  

   Package meta data must be scanned into Meta Data Services to track lineage.

   

9. Select the Resolve Package References To Scanned Catalog Meta Data check box, which links the package to meta data stored in Meta Data Services.

10. After you link the package to stored meta data, you must specify settings for scanning package meta data into Meta Data Services and for keeping that information current. By default, catalogs that were previously scanned into Meta Data Services are used, and these catalogs are not refreshed each time you save a package. This default saves time when a package is saved but will cause problems when the underlying meta data has changed. With a new package, you should scan all information into Meta Data Services.

11. Click Scan All Referenced Catalogs Into Repository, click Scan Catalog Always, and then click OK.

12. Click OK to save the package to Meta Data Services. Saving this small package will take about 15 “30 seconds. (Saving and scanning a large package will take more time.)

Now that you have linked the package to Meta Data Services, recorded meta data into Meta Data Services, and saved the package to Meta Data Services, you are ready to learn how to implement row-level data lineage to track data transformations.

Enable row-level data lineage

In this procedure, you will learn how to enable row-level data auditing and write data lineage information to Meta Data Services. Row-level lineage also enables you to track changes to package meta data across package versions, determine which package version produced a particular set of transformations, and track package execution information.

1. Right-click an open area on the LoadHistoricalData design sheet and then click Package Properties.

2. Click the Advanced tab to display two lineage options.

   

3. Select the Show Lineage Variables As Source Columns check box, select the Write Lineage To Repository check box, and then click OK.

   These options enable the generation of lineage information at package run time. DTS supplies two types of column identifiers when you move data from a data source to a destination table: a short identifier called DTSLineage_Short , and a long identifier called DTSLineage_Full . The long identifier is a GUID, and the short identifier is a checksum of the package version. The checksum value uses less space but is not guaranteed to be unique, though duplicates are unlikely . To take advantage of this generated lineage information, you must store these variables in columns in your destination table while a column in each row of data is transformed and stored in the destination table.

   Tip  

   Although the ability of Meta Data Services to track data lineage is very useful, it comes at the cost of considerable performance overhead. You might want to use this feature for testing and debugging only, or for situations in which row-level lineage is an absolute requirement.

Now that you have enabled row-level data lineage in the LoadHistoricalData package, you are ready to add the columns to hold this data lineage information and configure the Load SalesFact Table task to use these columns.

Configure the Load SalesFact Table task to use lineage columns

1. On the Windows menu in SQL Server Enterprise Manager, click Console Root\Microsoft SQL Servers\SQL Server Group\(LOCAL)(Windows NT)\Data Transformation Services\Local Packages.

2. On the Tools menu, click SQL Query Analyzer.

3. On the SQL Query Analyzer toolbar, click Load SQL Script.

4. In the Look In list, navigate to C:\Microsoft Press\SQL DTS SBS\Ch3\ChapterFiles and then double-click AddColumnsToSalesFactTable.sql.

   This script adds the two columns to the SalesFact table in the SBS_OLAP database that are required to demonstrate the data lineage feature.

   

5. On the toolbar, click Execute to add these two columns.

6. Switch to the LoadHistoricalData package in DTS Designer.

7. On the design sheet, double-click the Load SalesFact Table task and then click the Destination tab.

   The schema for the SalesFact table in the SBS_OLAP database now contains two lineage columns: LineageFull and LineageShort .

8. Click the Transformations tab to display the Source column list, which includes columns called DTSLineage_Full and DTSLineage_Short .

   You can map these columns to the destination columns you created in the destination table. The values for these columns are generated by DTS at run time.

   

9. Click Delete All and then click Select All.

10. Click New, select Copy Column, and then click OK.

11. Click OK to create the default copy column transformations.

    The data lineage columns have now been correctly mapped.

    

12. Click OK to close the Transform Data Task Properties dialog box and save the transformation changes.

13. On the toolbar, click Save.

You have successfully implemented both column-level and row-level data lineage for the LoadHistoricalData package that you stored in Meta Data Services. You are now ready to test this feature and browse the meta data stored in Meta Data Services.

Test the data lineage feature and use the Metadata Browser

In this procedure, you will execute the LoadHistoricalData package stored in Meta Data Services, view the data lineage information recorded in the SalesFact table when the package executed, and use the Metadata Browser to view the package meta data stored in the Meta Data Services repository.

1. On the DTS Designer toolbar, click Execute.

2. When the package completes, click OK to close the Package Execution Results message box, and then click Done to close the Executing DTS Package: LoadHistoricalData dialog box.

3. Close the LoadHistoricalData package in DTS Designer.

4. In the SQL Server Enterprise Manager console tree, expand Databases, expand SBS_OLAP, and then click Tables.

5. In the details pane, right-click SalesFact, point to Open Table, and then click Return All Rows.

   The LineageFull and LineageShort columns for all rows in this table have been populated with lineage variable values that identify the package that populated them. If you take advantage of this Row Level Lineage feature, when the data is modified by subsequent package executions, the lineage column can track the packages that modified each particular row.

   

6. Select and copy the checksum value displayed in the LineageShort column. (You will enter this value into the Metadata Browser in the next procedure.)

7. Close the SQL Server Enterprise Manager “ [Data in Table ˜SalesFact in ˜SBS_OLAP on ˜(local) ] window.

8. In the console tree, expand Data Transformation Services and then click Meta Data to display the Metadata Browser in the details pane.

   Depending upon your security settings in Microsoft Internet Explorer, you might receive a prompt informing you that an ActiveX control in SQL Server Enterprise Manager is attempting to interact with other parts of the page. If so, click Yes. Notice that meta data about the SBS_OLAP database appears on the Browse tab.

   Tip  

   If the Metadata Browser does not appear, click the View menu and then click Metadata Browser. To hide the console tree so that you see only the details pane, click the Show/Hide Console Tree icon on the toolbar.

9. On the Browse tab in the Metadata Browser, expand the SBS_OLAP database, expand dbo, and then expand SalesFact to display meta data about this user table.

   

10. Hover your cursor over the yellow circle containing the black triangle next to the ProductKey column in the SalesFact table on the Browse tab to display the Packages and Source/Destination links.

    These links enable you to display information about packages affecting this column, the sources of data used to populate this column, and the destinations that get data from this column.

    

11. Click the Lineage tab and then paste the copied value from the LineageShort column into the Lineage Short text box.

    

12. Click Find Lineage to display details about the package version responsible for generating this lineage value in the details pane.

    Details include the package creation date, package version creation time, modification time, exception log (if any), the system the package was executed on, the user who executed to the package, and the time the package was executed.

    

13. Click the Package tab to display each package stored in Meta Data Services.

    You can expand each version of a package to obtain the following: details about when the package was created and by whom, a description of the package, comments about the package, details about when the package was last executed and by whom, and the location of the exception log (if any) for the package.

Now that you have learned how to save a package using each of the available formats, you are ready to learn how to keep your packages secure and how to prevent unauthorized users from creating packages.