Export Package from File System to SSIS Package Store on another SQL Server Instance using SQL Server Management Studio (SSMS)
1. Connect to SQL Server 2008 Integration Services using SQL Server Management Studio.
2. To export an SSIS Package from File System to SSIS Package Store on another Instance of SQL Server, you need to expand the File System node and then right click the SSIS package which needs to be exported and select Export Package…. from the popup window as shown in the snippet below.
3. This will open up Export Package dialog box as shown in the below snippet.
In Export Package screen you need to select Package Location as “SSIS Package Store” from the drop-down list. Next, you need to provide the SQL Server Instance name where you want to export the SSIS package (You won’t be able to export a SSIS package residing on SQL Server 2008 to an SSIS Package Store of SQL Server 2005). In order to save the SSIS package you need to click “….” button in Package path which will open up SSIS Package window where you need to choose the folder within SQL Server where you want to save the SSIS package. Here you can even provide a new name for the package while specifying the save location for the package within SQL Server. Click OK to close the SSIS Package window. By default, the Protection Level value for the exported package remains as “Encrypt sensitive data with user key”. If you want to change the protection level then you can choose the appropriate one by clicking “….” button and then finally click OK to export package. Once the package is successfully exported it can be viewed by connecting to Integration Services of the destination SQL Server and then expand Stored Packages | File System.
View Exported SSIS Packages in MSDB
All the SSIS packages which you have exported to MSDB using the Export Package can be viewed by connecting to Integration Services using SQL Server Management Studio (SSMS). Once you are connected to Integration Services then expand Stored Packages | MSDB node, there you will be able to see all the SSIS packages. The SSIS packages which are exported into MSDB database are stored in SYSDTSPACKAGES table in SQL Server 2005 & SYSSSISPACKAGES table in SQL Server 2008. When MSDB database is backed up by the database administrator all these packages are automatically backed up. If the SSIS packages are stored as File System within SQL Server “Drive:Program FilesMicrosoft SQL Server100DTSPackages” location then the .DTSX files needs to be backed like any other files whenever they are changed to a secured location using SQL Server Agent Jobs.
Deleting an SSIS Package Using SQL Server Management Studio (SSMS)
Deleting an SSIS Package in SQL Server Management Studio is very easy. You can right click the desired package and select Delete from the drop-down list as shown in the below snippet.
This will open up Confirm Package Delete dialog box as shown in the below snippet. Click “Yes” to delete the SSIS Package. In the same way you can delete SSIS packages which are stored in MSDB database.
Conclusion
In this article you have seen the steps which you need to follow in order to export an SQL Server Integration Services Packages that is stored in SQL Server to another instance of SQL Server, or a File system, or a SSIS Package store using SQL Server Management Studio (SSMS). The same steps you can follow in order to export an SSIS packages from SQL Server 2005 to another instance of SQL Server 2005 or SQL Server 2008. However you won’t be able to export an SSIS Package from SQL Server 2008 to SQL Server 2005 Instance.
]]>