It is a very common task for Database Administrators these days to import SQL Server Integration Services Packages from one instance of SQL Server or from a File system into a local instance of SQL Server when setting up different environments to support applications. In this article you will see the steps which you need to follow in order to import a SQL Server Integration Services Package that is stored in an instance of SQL Server, or in File system, or in SSIS Package store using SQL Server Management Studio (SSMS).
Different Methods for Importing SSIS Packages into SQL Server
The most commonly used methods for importing SQL Server Integration Service (SSIS) Packages into SQL Server 2005 or in SQL Server 2008 are:-
- Using Import Package in SQL Server Management Studio (SSMS)
- Using DTUTIL Command Prompt Utility
However in this article you will see how to use import package in SQL Server Management Studio (SSMS) to import SSIS packages.
Using Import Package in SQL Server Management Studio (SSMS)
You can use Import Package in SQL Server Management Studio (SSMS) to import 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.
Import 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 import an SSIS Package into File System, you need to right click the File System node and select Import Package…. from the popup window as shown in the snippet below.
To import an SSIS Package into MSDB, you need to right click the MSDB node and select Import Package…. from the popup window as shown in the snippet below
3. This will open up Import Package dialog box as shown in the snippet below.
In Import 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 import an SSIS package which is saved as .DTSX file. In order to choose the SSIS package you need to click “….” button in Package path and choose the SSIS package path from “C:CustomerDataSalesCustomer.dtsx” as shown in the above snippet. Once the package path is provided correctly, the name of the SSIS package is automatically populated within the Package name text box. If you want to store the package with a different name then you can provide a new name within package name text box. By default, the Protection Level value for the imported package remains as “Keep protection level of the original package”. If you want to change the protection level then you can choose the appropriate one by clicking “….” button and then finally click OK to import package. Once the package is successfully imported it can be viewed by connecting to Integration Services and then expand Stored Packages | File System.