Import Package from SQL Server 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 from SQL Server, you need to right click the File System node and select Import Package…. from the popup window.
3. This will open up Import Package dialog box as shown in the below snippet.
In Import Package screen you need to select Package Location as “SQL Server” from the drop-down list. Next, you need to provide the SQL Server name from where you want to import the SSIS package. Then, you need to choose the appropriate authentication mode to connect to the SQL Server. In order to choose the SSIS package you need to click “….” button in Package path which will open up SSIS Package window as shown in the above snippet. In the SSIS package window select “SamplePackage” and click OK to save the selection. Once the package details are provided correctly, the name of the SSIS package is automatically populated within the Package name text box. If you want to import 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.
Import Package from SSIS Package Store 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 from SSIS Package Store, you need to right click the File System node and select Import Package…. from the popup window.
3. This will open up Import Package dialog box as shown in the below snippet.
In Import 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 name from where you want to import the SSIS package. In order to choose the SSIS package you need to click “….” button in Package path which will open up SSIS Package window as shown in the above snippet. In the SSIS package window select “SalesCustomer” and click OK to save the selection. Once the package details are provided correctly, the name of the SSIS package is automatically populated within the Package name text box. This time let us import the package with a different name. So let us provide the package name as “CustomerSales” 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.
View Imported SSIS Packages as File System
All the SSIS packages which you have imported as File System using the Import 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 node and then expand File System node, there you will be able to see all the three SSIS packages which you have imported as shown in the snippet below.
When packages are imported into SQL Server 2008 as File System then these imported packages are stored as .DTSX files within “Drive:Program FilesMicrosoft SQL Server100DTSPackages” folder as shown in the snippet below. However, important thing which database administrators’ needs to keep in their mind is that the SSIS packages which are imported as File System will not be backed up when you are taking system database backups. You need to have a job which manually takes the file copy of these packages to a secured location whenever they are changed.
