Deploying Integration Services packages using Import/Export feature in SSMS

You can use this method to import or export packages to or from SQL Server or file system. To import packages, connect to your instance of Integration Services using SQL Server Management Studio(SSMS), expand the Stored Packages folder, right-click the folder you wish to import the packages into and selectImport Package as shown in the figure below.

The Import Package dialog box appears as shown in the figure below.

In the Package location drop-down, you have three options to select:

• SQL Server: Choose this option if you wish to import the package from SQL Server.
• File System: Choose this option if you wish to import the package from anywhere on the file system.
• SSIS Package Store: This option basically refers to the location Program Files\Microsoft SQL Server\100\DTS\Packages. If the original package was stored under this location while deployment, then you can use this option to import the package into SQL Server.

Under the Server drop down, choose the server you wish to import the package from. If you have selected thePackage location to be SQL Server, then you will have to specify the authentication to be used to login to the server to access the package. Also, you will have to specify a user name and a password if you have selectedSQL Server Authentication.
In the Package Path, click the ellipsis button to specify the location where the package to import is stored.

• If you have selected the Package location to be SQL Server, then you would specify the folder underStored Packages in SQL Server, it can either be in File System or MSDB database.
• If you have selected the Package location to be File System, then you would specify the location on the file system where the exported package will be stored.
• If you have selected the Package location to be SSIS Package Store, then you would specify the location under
  Stored Packages in SQL Server, it can either be in File System or MSDB database.

The difference between File System and SSIS Package Store is that, the packages stored in SSIS Package Store are managed by the Integration Services service and these packages will show up in the File Systemunder Stored Packages in SQL Server Management Studio(SSMS) when you connect to Integration Services.
In the Package name, you can specify a different name to the package being imported.
In the Protection level, you can either keep the protection level of the original package or specify a different protection level based on your requirement.
The process of exporting a package is pretty much the same as importing a package. You just have to right-click the package you want to export and select Export Package. The Export Package dialog box appears as shown below.

In the Package location drop-down, you have three options to select:

• SQL Server: Choose this option if you wish to export the package to SQL Server.
• File System: Choose this option if you wish to export the package to a file system.
• SSIS Package Store: This option basically refers to the location Program Files\Microsoft SQL Server\10\DTS\Packages.

Under the Server drop down, choose the server you wish to export the package to. If you have selected thePackage location to be SQL Server, then you will have to specify the authentication to be used to login to the server to deploy the package. Also, you will have to specify a user name and a password if you have selected SQL Server Authentication.
In the Package Path, click the ellipsis button to specify the location where the package has to be exported.

• If you have selected the Package location to be SQL Server, then you would specify the folder underStored Packages in SQL Server, it can either be in File System or MSDB database.
• If you have selected the Package location to be File System, then you would specify the location on the file system where the exported package will be stored.
• If you have selected the Package location to be SSIS Package Store, then you would specify the location under
  Stored Packages in SQL Server, it can either be in File System or MSDB database.

In the Protection level, you can either keep the protection level of the original package or specify a different protection level based on your requirement.
Securing Packages that are deployed using Import/Export method:
When using the Import Export feature in SQL Server management studio(SSMS), you can import packages to store in SQL Server or export packages to store them in either SQL Server or File System.

• Securing packages stored in SQL Server:

Integration Services packages that are stored in SQL Server can be encrypted using the Package Protection Level property as well as stored inside MSDB database, where the access to the package is controlled by Integration Services database roles. Database roles are assigned to packages using SQL Server Management Studio. However, there may be additional files(configuration files, checkpoint files, log files etc) that need to be deployed as well. If you have sensitive information in the additional files, access to those files can be controlled by using folder/file permissions at the operating system level.Here is a brief overview of the built-in database roles available with Integration Services:
db_ssisadmin - This role has unlimited privileges to all the integration services packages stored on the server. In short, this role has god rights.
db_ssisoperator - This role has all the read permissions(enumerate, view, execute, export) but no write permissions(import,delete).
db_ssisltduser - Users belonging to this role will have god rights on the packages owned by them. Furthermore, the users will be able to enumerate all the packages.
Please note that the names of the built-in database roles have changed between SSIS 2005 and SSIS 2008. Here is the comparison:
db_dtsadmin in SQL Server 2005 is db_ssisadmin in SQL Server 2008
db_dtsltduser in SQL Server 2005 is db_ssisltduser in SQL Server 2008
db_dtsoperator in SQL Server 2005 is db_ssisoperator in SQL Server 2008.

• Securing packages stored in File System

Integration Services packages that are stored in file system can be encrypted using the Package Protection Level property as well as by controlling access to the package and the dependent files(configuration files, checkpoint files, log files etc) by using folder/file permissions at the operating system level.
Please note that the Package Protection Level does NOT protect files that are stored outside the Integration Services package. For example configuration files, checkpoint files, log files will not be protected by the package protection level, so you will have to secure them using some other means such as folder/file permissions at the operating system level.