Package Metadata and Storage Management

As you've learned in previous chapters, SQL Server 2005 provides extensive support for XML, such as the XML data type, XQuery, XML showplan and statistics, native HTTP endpoint XML Web service, and more. SSIS, as a major component of SQL Server 2005, is no exception.

For example, various project files such as .ssmssqlproj, .dwproj, .dtproj, and so on are saved as XML documents. In addition, SSIS Mapping files, located in the %ProgramFiles%\Microsoft SQL Server\90\DTS\ MappingFiles folder, which map data types from one back-end to the other, also use XML extensively. If you're interested in learning the nuances of data type differences among DB2, Oracle, SQL Server, and Access, you can open them up and take a look there. The package configuration file, .dtsConfig, is also an XML file.

As was alluded to many times in previous chapters, SSIS packages can be stored as a dtsx file, which is an XML file. Data sources for the project are stored in *.ds files.

Traditionally, in SQL Server 2000 and SQL Server 7, the most popular storage type has been MSDB. In SQL Server 2000 and SQL Server 7, there are a few tables that store packages, as shown in Figure 12-16.

Figure 12-16

In SQL Server 2005, you still have the option of saving packages into MSDB, although the table structure has changed somewhat, as you can see in Figure 12-17.

Figure 12-17

In general, XML files are easier to read and modify than packages saved in structured storage like MSDB, because XML files are text-based. In addition, there are more utilities and tools available for XML file processing. It is believed that XML file-based SSIS packages will be the future.

In addition, because of the separation of Control Flow and Data Flow in SSIS, it is much easier to change the properties of a package if it is XML-based. For example, you can easily modify the connection property, file directory, and other related properties of a package using a simple text editor, without having to using BIDS. That can be handy at times, especially when you deploy things to production servers.

On the other hand, MSDB-based storage has its merits, especially when it comes to backing up and restoring. Packages saved to MSDB can be backed up and restored using SQL Server's backup and restore features. Note that if packages are saved in MSDB, but their configuration files are not, then you need to make sure that the configuration files are backed up regularly as part of securing packages saved to MSDB. To include configurations in the backup of the MSDB database, you should consider using the SQL Server configuration type instead of file-based configurations.

The backup of packages that are saved to the file system should be included in the backup plan for securing those packages. The Integration Services service configuration file, which has the default name MsDtsSrvr.ini.xml, lists the folders on the server that the service monitors. You should make sure these folders are backed up. Additionally, packages may be stored in other folders on the server, and you should make sure to include these folders in the backup.

Managing SSIS Packages within SQL Server Management Studio

In SQL Server Management Studio, if you connect to an Integration Service instance in Object Explorer, you will see two folders: Running Packages and Stored Packages, as shown in Figure 12-18.

Figure 12-18

The Running Packages folder lists all packages that are currently running. Not surprisingly, the contents in this folder will change to reflect your current SSIS package activity. You must manually refresh this folder to display currently running packages.

The Stored Packages folder lists packages that have been saved and registered on this Integration Service instance. This folder has two default subfolders: File System and MSDB.

When you browse packages within SQL Server Management Studio, SQL Server Integration Services will go to the HKLM\SOFTWARE\Microsoft\MSDTS\ServiceConfigFile registry key to look for the SSIS service configuration file. Not surprisingly, the File System and MSDB Folder property is controlled by an XML configuration file. By default, this file is named MsDtsSrvr.ini.xml and is located at %ProgramFiles%\Microsoft SQL Server\90\DTS\Binn folder. The following is the default content of this XML file:

 <?xml version="1.0" encoding="utf-8"?> <DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">   <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>   <TopLevelFolders>     <Folder xsi:type="SqlServerFolder">       <Name>MSDB</Name>       <ServerName>.</ServerName>     </Folder>     <Folder xsi:type="FileSystemFolder">       <Name>File System</Name>       <StorePath>..\Packages</StorePath>     </Folder>     <Folder xsi:type="FileSystemFolder">       <Name>Dev Packages</Name>       <StorePath>c:\SSIS</StorePath>     </Folder>   </TopLevelFolders> </DtsServiceConfiguration> 

You can customize this initialization file to suit your needs. For example, if you want to group your packages into Dev, QA, Cert, and Prod, you can add the following code to this file, along with the correct StorePath parameter. This way, all packages are grouped logically and therefore easier to manage.

For example, suppose you have a folder called C:\SSIS that you use for your packages. Underneath that, you can create subfolders called DevDTSX, QADTSX, CertDTSX, and ProdDTSX. You can then modify your configuration file using the following code:

 <?xml version="1.0" encoding="utf-8"?> <DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">   <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>   <TopLevelFolders>     <Folder xsi:type="SqlServerFolder">       <Name>MSDB</Name>       <ServerName>.</ServerName>     </Folder>     <Folder xsi:type="FileSystemFolder">       <Name>DevDTSX</Name>       <StorePath>c:\SSIS\Dev</StorePath>     </Folder>     <Folder xsi:type="FileSystemFolder">       <Name>QADTSX</Name>       <StorePath>c:\SSIS\QA</StorePath>     </Folder>     <Folder xsi:type="FileSystemFolder">       <Name>CertDTSX</Name>       <StorePath>c:\SSIS\Cert</StorePath>     </Folder>     <Folder xsi:type="FileSystemFolder">       <Name>ProdDTSX</Name>       <StorePath>c:\SSIS\Prod</StorePath>     </Folder>   </TopLevelFolders> </DtsServiceConfiguration> 

After you make the modification to the configuration file, you need to restart SQL Server Integration Services for the new configuration to take effect. Figure 12-19 shows the new folder structure.

Figure 12-19

Notice that StorePath can be a UNC path. For example, if you store all your dtsx packages in one shared folder on a server, you can use that too. Just use \\PackageServerName\SharedPackageFolderName as StorePath. In fact, this is probably a good way to share packages among a group of developers.

In addition, if you decide to store some packages in MSDB, it probably is a good idea to store them on one server. That way, you can manage them centrally in one place.

In an environment where you have a lot of servers to manage, you can create a single MsDtsSrvr.ini.xml file and distribute it to all the servers that you use. That way, you will have all the packages you manage in one place and they will be uniform in all the servers you touch. That will truly make the management of packages portable.

Please note that although you can manage SSIS packages using SQL Server Management Studio, you cannot create packages with it. You will need BIDS to do that.

Managing DTS 2000 Packages within SQL Server Management Studio

If the SQL Server 2000 DTS runtime is installed, you can use SQL Server Management Studio to manage and even edit your DTS 2000 packages stored on a SQL Server 2000 MSDB database.

First, you need to connect to the SQL Server 2000 database instance in Object Explorer where those packages are stored. This is somewhat counterintuitive in that you do not connect to Integration Service to manage those packages.

Once you are connected, you need to expand the Management folder, then expand the Legacy folder, and then expand Data Transformation Services. As you can see in Figure 12-20, the DTS 2000 package you created earlier is listed.

Figure 12-20

By right-clicking the package, you will see a context menu, where you can open, migrate, export, delete, rename, and refresh the highlighted package (see Figure 12-21).

Figure 12-21