Keywords: SSIS | SQL Server Management Studio | Package Management
Abstract: This article provides a comprehensive guide on connecting to Integration Services and viewing SSIS packages in SQL Server Management Studio. It covers SSIS package storage mechanisms, package management functionalities, detailed connection procedures, common issue resolutions, and package import/export operations. Through in-depth analysis of package storage structures and service configurations, it helps users master SSIS package management techniques.
SSIS Package Management Overview
SQL Server Integration Services (SSIS) is Microsoft's data integration and workflow platform, widely used for ETL (Extract, Transform, Load) processes. Managing SSIS packages in SQL Server Management Studio (SSMS) is a common task for data engineers. Many users encounter difficulties locating created packages initially, often due to misunderstandings about SSMS connection types.
Connecting to Integration Services
To view SSIS packages, you must first establish a proper connection to the Integration Services service. When launching SSMS, in the "Connect to Server" dialog, you must select "Integration Services" as the server type instead of the default "Database Engine". The server name should specify the computer name or IP address where the Integration Services service is running.
If connection fails after successful authentication, it's typically because the Integration Services service isn't running. Check the service status via SQL Server Configuration Manager: locate it in the Start menu under "SQL Server Configuration Tools", select "SQL Server Services", and ensure the "SQL Server Integration Services" service status shows "Running". Start the service if necessary.
SSIS Package Storage Architecture
The Integration Services service manages packages from two primary storage locations: the file system and the msdb database. The package store is a logical concept encompassing all monitored locations specified in the configuration file.
In Object Explorer, expanding the "Stored Packages" folder reveals two default subfolders: "File System" and "MSDB". The "File System" folder displays packages saved to the file system, with the default location being %Program Files%\Microsoft SQL Server\100\DTS\Packages. The "MSDB" folder shows packages stored in the SQL Server msdb database, where they reside in the sysssispackages system table.
The logical folder mechanism allows users to organize packages in msdb by purpose. These folders are defined in the sysssispackagefolders table, with the folder hierarchy established through the folderid and parentfolderid columns. Root folders have NULL values in the parentfolderid column.
Viewing and Managing Packages
After successful connection, expand the "Stored Packages" folder and its subfolders in Object Explorer to view all managed packages. Each package displays information such as name, version, and description.
The "Running Packages" folder shows packages currently executing on the server. The Summary page provides real-time information like execution duration. To stop a running package, simply right-click it and select the "Stop" option.
Package Import and Export
SSMS provides comprehensive package import and export functionality, supporting transfers between file system, package store, and msdb database.
To import a package, right-click the target folder, select "Import Package", then choose the source type: SQL Server instance, file system, or SSIS Package Store. Provide server information, authentication method, and package path based on your selection. You can update the package name and protection level during this process.
Exporting packages follows a similar procedure: right-click the package to export, select "Export", and specify the destination location and format. Protection levels can be adjusted during import/export, supporting encryption of sensitive data or all data using passwords.
Configuration and Backup Considerations
Integration Services service behavior is controlled by a configuration file, default named MsDtsSrvr.ini.xml. This file specifies the file system folders monitored by the service. To manage packages in other locations, you need to modify this configuration file.
For package backup, packages saved in msdb can be protected using SQL Server's backup and restore features. However, package configuration files and file system packages require separate backup plans. Using SQL Server configuration type instead of file-based configurations is recommended to include configurations in database backups.
Common Issue Resolution
Users frequently report inability to find the "Execute SSIS Package" option in job scheduler, typically because they're connected to Database Engine instead of Integration Services service. Ensuring the correct server type is key to resolving this issue.
Another common problem involves packages existing in the file system but not appearing in SSMS, usually because the file's folder isn't included in the Integration Services service configuration file. Checking and updating the configuration file resolves this.
Version compatibility is also important: SQL Server 2008 can import packages from SQL Server 2005 but cannot export to 2005, while SQL Server 2005 cannot import from or export to SQL Server 2008.