Import and Export Packages (SSIS Service)
Important
This topic discusses the Integration Services service, a Windows service for managing Integration Services packages. SQL Server 2012 supports the service for backward compatibility with earlier releases of Integration Services. Starting in SQL Server 2012, you can manage objects such as packages on the Integration Services server.
Packages can be saved either in the sysssispackages table in the SQL Server msdb database or in the file system.
The package store, which is the logical storage that Integration Services service monitors and manages, can include both the msdb database and the file system folders specified in the configuration file for the Integration Services service.
You can import and export packages between the following storage types:
File system folders anywhere in the file system.
Folders in the SSIS Package Store. The two default folders are named File System and MSDB.
The SQL Server msdb database.
Integration Services gives you the ability to import and export packages, and by doing this change the storage format and location of packages. Using the import and export features, you can add packages to the file system, package store, or msdb database, and copy packages from one storage format to another. For example, packages saved in msdb can be copied to the file system and vice versa.
You can also copy a package to a different format using the dtutil command prompt utility (dtutil.exe). For more information, see dtutil Utility.
To import or export a package
Important
This topic discusses the Integration Services service that is part of SQL Server 2008. SQL Server 2012 supports the Integration Services service for backward compatibility with SQL Server 2008. For information about managing packages in SQL Server 2012, see Integration Services (SSIS) Server.
You can import or export an Integration Services package from or to the following locations:
You can import a package that is stored in an instance of Microsoft SQL Server, in the file system, or in the SSIS package store. The imported package is saved to SQL Server or to a folder in the SSIS package store.
You can export a package that is stored in an instance of SQL Server, the file system, or the SSIS Package Store to a different storage format and location.
However, there are some restrictions on importing and exporting a package between different versions of SQL Server:
On an instance of SQL Server 2008, you can import packages from an instance of SQL Server 2005, but you cannot export packages to an instance of SQL Server 2005.
On an instance of SQL Server 2005, you cannot import packages from, or export packages to, an instance of SQL Server 2008.
The following procedures describe how to use SQL Server Management Studio to import or export a package.
To import a package by Using SQL Server Management Studio
Click Start, point to Microsoft SQL Server, and then click SQL Server Management Studio.
In the Connect to Server dialog box set the following options:
In the Server type box, select Integration Services.
In the Server name box, provide a server name or click <Browse for more…> and locate the server to use.
If Object Explorer is not open, on the View menu, click Object Explorer.
In Object Explorer, expand the Stored Packages folder.
Expand the subfolders to locate the folder into which you want to import a package.
Right-click the folder, click Import Package. and then do one of the following:
To import from an instance of SQL Server, select the SQL Server option, and then specify the server and select the authentication mode. If you select SQL Server Authentication, provide a user name and a password.
Click the browse button (…), select the package to import, and then click OK.
To import from the file system, select the File system option.
Click the browse button (…), select the package to import, and then click Open.
To import from the SSIS Package Store, select the SSIS Package Store option and specify the server.
Click the browse button (…), select the package to import, and then click OK.
Optionally, update the package name.
To update the protection level of the package, click the browse button (…) and choose a different protection level by using the Package Protection Level dialog box. If the Encrypt sensitive data with password or the Encrypt all data with password option is selected, type and confirm a password.
Click OK to complete the import.
To export a package by Using SQL Server Management Studio
Click Start, point to Microsoft SQL Server, and then click SQL Server Management Studio.
In the Connect to Server dialog box, set the following options:
In the Server type box, select Integration Services.
In the Server name box, provide a server name or click <Browse for more…> and locate the server to use.
If Object Explorer is not open, on the View menu, click Object Explorer.
In Object Explorer, expand the Stored Packages folder.
Expand the subfolders to locate the package you want to export.
Right-click the package, click Export, and then do one of the following:
To export to an instance of SQL Server, select the SQL Server option, and then specify the server and select the authentication mode. If you select SQL Server Authentication, provide a user name and a password.
Click the browse button (…), and expand the SSIS Packages folder to locate the folder to which you want to save the package. Optionally, update the default name of the package, and then click OK.
To export to the file system, select the File System option.
Click the browse button (…) to locate the folder to which you want to export the package, type the name of the package file, and then click Save.
To export to the SSIS package store, select the SSIS Package Store option, and specify the server.
Click the browse button (…), expand the SSIS Packages folder, and select the folder to which you want to save the package. Optionally, enter a new name for the package in the Package Name text box. Click OK.
To update the protection level of the package, click the browse button (…) and choose a different protection level by using the Package Protection Level dialog box. If the Encrypt sensitive data with password or the Encrypt all data with password option is selected, type and confirm a password.
Click OK to complete the export.