Introduction of SSIS

What is SSIS?

SSIS stands for SQL Server Integration Services. It is one of the services of SQL Server and was introduced for the first time in SQL Server 2005. It’s a powerful replacement for the legacy DTS (Data Transformation Services) that exists in SQL Server 2000.
Installation of Integration Services:

Feature Selection screen is the step where you will manage the abilities of your database instance.

Shared features:

Shared features for a major release are used only by multiple instances of the same version of SQL Server; other features are shared only across minor version instances

Integration services:

Includes the designer, runtime, and utilities that enable Integration Services to move, integrate, and transform data between data stores.

In Server Configuration screen you can specify the Service Account under which SQL Server Integration Services should be running. Here you can also specify the Startup Type for each of the SQL Server Services.

Roles & Permissions:  

SQL Server Integration Services includes the three fixed database-level roles, db_ssisadmin, db_ssisltduser, and db_ssisoperator, for controlling access to packages. 

Roles db_ssisadmin, ds_ssisltduser and db_ssisoperator can be found within the system database msdb.

SSIS related roles in msdb database 

db_ssisadmin

db_ssisltduser 

db_ssisoperator 

sysadmin -- enumerate all pkg, delete all, change all packages.

db_ssisadmin --

ssis_admin have full administrative access to ssis catalog, enumerate owned packages --import and delete own pkg 

db_ssisltduser --

Import delete, change own 

db_ssisoperator --enum, view, execute all 

win admin -- Stop all pkg

To grant role to user using TSQL:

USE msdb;
GO

EXEC sp_addrolemember N'db_dtsltduser', N'{User Name Here}';
GO

The sysssispackages table in msdb contains the packages that are saved to SQL Server. 

To open Object Explorer and connect to Integration Services

1. Open SQL Server Management Studio.
2. Click Object Explorer on the View menu.
3. On the Object Explorer toolbar, click Connect, and then click Integration Services.
4. In the Connect to Server dialog box, provide a server name. You can use a period (.), (local), or localhost to indicate the local server.
5. Click Connect.

---