MS SQL Server - Integration Services

This service is used to carry out ETL (Extraction, Transform and Load data) and admin operations. The BIDS (Business Intelligence Studio till 2008 R2) and SSDT (SQL Server Data Tools from 2012) are the environments to develop packages.

SSIS Basic Architecture

Solution (Collection of projects) ---> Project (Collection of packages) ---> Package (Collection of tasks for ETL and admin operations)

Under Package, the following components are available −

• Control Flow (Containers and Tasks)
• Data Flow (Source, Transformations, Destinations)
• Event Handler (Sending of messages, Emails)
• Package Explorer (A single view for all in package)
• Parameters (User interaction)

Following are the steps to open BIDS\SSDT.

Step 1 − Open either BIDS\SSDT based on the version from the Microsoft SQL Server programs group. The following screen appears.

Step 2 − The above screen shows SSDT has opened. Go to file at the top left corner in the above image and click New. Select project and the following screen opens.

Step 3 − Select Integration Services under Business Intelligence on the top left corner in the above screen to get the following screen.

Step 4 − In the above screen, select either Integration Services Project or Integration Services Import Project Wizard based on your requirement to develop\create the package.