SSIS

SQL Server Integration Services (SSIS) is a tool that we use to perform ETL (extract, transform and load data) operations. SSIS provides the ability to:

• • retrieve data from just about any source

  • perform various transformations on the data; e.g. convert from one type to another, convert to uppercase or lowercase, perform calculations, etc.

  • load data into just about any source

  • define a workflow

You can Create SSIS packages with SQL Server Management Studio (SSMS). SQL Server Management Studio (SSMS) provides Import and Export Wizard tasks which you can use to copy data from one data source to another. You can choose from a variety of source and destination data source types, select tables to copy or specify your own query to extract data, and save your work as an SSIS package.

The main tool to create SSIS is by Business Intelligence Development Studio (BIDS). The BIDS pattern for developing SSIS packages is based on the concept of projects and solutions. A project is a container for one or more SSIS packages. Projects can be used to organize SSIS packages by gathering all the "ssis'es" that belong to some subject in one project. A solution is a container for one or more projects. When you create multiple SSIS projects to organize your packages, you combine those packages into one or more solutions.

To create SSIS do the following steps:

• 1. launch the SQL Server Business Intelligence Development Studio

  2. Click File, New, Project on the top level menu to display the New Project dialog. Select Business Intelligence Projects as the project type, then Integration Services Project as the template. Type name for the Project and for the Solution and define the location to save them. By default a new SSIS package is added when you create an Integration Services Project.

  3. Click on the new package and use the Designer to define the package. There are four parts related to generate package:

     1. The designer - the large area in the middle of the window. It Contains four tabs:

        1. The Control Flow tab contains the tasks that the SSIS package performs and the flow from one task to another.

        2. The Data Flow tab is another designer that contains the details for a given data flow task; e.g. retrieve data from some data source, optionally perform some transformations on the data, then write it to some other data source.

        3. The Event Handlers tab is yet another designer where we can specify tasks to be performed when a particular event is raised.

        4. The Package Explorer tab represents the entire package in a tree-view.

     2. The Connection Managers window contains the various data sources and destinations that the package uses. The Connection Managers are defined once then referenced in the various tasks such as the Execute SQL Task, an OLEDB Data Source, or a OLEDB Destination.

     3. The Toolbox contains the tasks that are available to the Control Flow, Data Flow or Event Handlers designers. To build a package you simply drag tasks from the Toolbox onto the designer and connect them in the order you want to execute.

     4. The Properties Window is available for us to edit and update the properties of a task in the designer, or a connection in the Connection Managers area. When you select some task, the properties window show the relevant properties of the task