Integrated services in MS SQL Server

Integrated services are used to implement ETL (Extraction, Transform and Load data) and administrator jobs. BIDS (Business Intelligence Studio until 2008 R2 version) and SSDT (SQL Server Data Tools from 2012 later) are the environments for this service.

SSIS Basic Architecture

Solution (a set of projects) → Project (a set of packages) → Package (a set of tasks for ETL and administrators).

Each package can include components:

1. Control Flow (Containers and Tasks)
2. Data Flow (Source, Transformation and Destination)
3. Event Handler (send message, Email)
4. Package Explorer (see all in 1 package)
5. Parameter (user interaction)

Here are the steps to open BIDS / SSDT.

Step 1 - Open the BIDS or SSDT from the MS SQL Server program group. The screen below will appear.

Integrated services in MS SQL Server Picture 1
Opened SSDT screen

Step 2 - Go to File in the left corner and select New > Project to open the screen below.

Integrated services in MS SQL Server Picture 2
Select the new Project

Step 3 - Select Integration Service under Business Intelligence in the upper left corner of the screen above to get the screen below.

Integrated services in MS SQL Server Picture 3
Integration Service opened

Step 4 - On the screen above, select the Integration Services Project service or the Interation Services Import Project Wizard according to your own requirements to create the package.

Previous article: Execution Plans - Execution plan in MS SQL Server

Next lesson: Analysis services in MS SQL Server