Professional SQL Server™ 2005 Integration Services

12.4. 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. 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 ...