Database Administrators sometimes need to export SQL Server Integration Services Package (SSIS) from one instance of SQL Server to another SQL Server Instance or a File system or a SSIS Package Store. In this article you will see the steps which you need to follow in order to export a SQL Server Integration Services Package that is stored as a File System in an instance of SQL Server to another SQL Server or a File system or a SSIS Package store using SQL Server Management Studio (SSMS).
Different Methods for Exporting SSIS Packages from SQL Server
The most commonly used methods for exporting SQL Server Integration Service (SSIS) Packages from SQL Server 2005 or in SQL Server 2008 are:
- Using Export Package in SQL Server Management Studio (SSMS)
- Using DTUTIL Command Prompt Utility
However in this article you will see how to use Export Package in SQL Server Management Studio (SSMS) to export SSIS package.
Using Export Package in SQL Server Management Studio (SSMS)
You can use Export Package in SQL Server Management Studio (SSMS) to export SSIS packages from three sources such as SQL Server Instance, File System or from SSIS Package Store. In this article we will explore all the three options.
Export Package from File System 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, 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.
To export an SSIS Package from MSDB, you need to expand the MSDB 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 snippet below.
In Export Package screen you need to select Package Location as “File System” from the drop-down list. However, there are three options available namely SQL Server, File System and SSIS Package Store. When you are selecting File System as an option for the Package location, then it means that you would like to export an SSIS package which is saved as .DTSX file within “Drive:Program FilesMicrosoft SQL Server100DTSPackages” folder. In order to save the SSIS package you need to click “….” button in Package path and provide the folder location where you want the SSIS package to be saved. In this example you will be saving the SalesCustomer.dtsx package to “C:SSISPackages” location as shown in the above snippet. Here you can even provide a new name while specifying the save location for the package. 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 in C:SSISPackages location.