Introduction to Business Intelligence Suite – SSIS

Standard

In earlier chapter, we have discussed about how to create simple SSIS package, how to migrate and manage the packages using Integration Service Wizard. Here we are going to discuss how to create, edit and debug the SSIS package using Business Intelligence Suite. As mentioned earlier we can modify the package it has been created earlier.

SQL Server Business Intelligence Development Studio comes together with SQL Server 2005 Developer edition.

Make sure with your about box of the Visual Studio 2005, it has SQL Server Integration Services templates for SSIS development. (Visual studio without SSIS template not useful for SSIS package development, Business Studio open with visual studio 2005 logo without visual basic or C# or web project templates not useful for corresponding projects development)

  1. Create New Project.

    Developer familiar with Microsoft development suite should not be much difficult to create the new solution and project. If you are not much familiar with any of the Microsoft product, it is not a matter, you can simply

  1. Select the File -> New -> Project … New project dialog box will be opened with available templates

  2. Select ‘Business Intelligence Project’ under ‘project type’, then select Integration services project’ from left side template pane.

  3. After select the location of the project and name, new project will be created with 4 default folder with one new dtsx package under SSIS packages folder. We can rename the package right click on it.

    Before design new package need to understand some of the terms.

    1. Data Source, Data Source view and Connection Manager

    Source and destination connections are created and managed by connection managers. Either right click on the connection manager to select the appropriate connection type directly

    or create the data source (Right click on the data sources folder under Solution Explorer to create new data source) first and then create ‘New Connection From Data Source…’.

    2. Control Flow (Control Flow Items and Maintenance Plan Task)

    3. Precedence Constraint

    4. Data Flow (Data Flow Source, Data Flow Transformation and Data Flow Destination)

    5. Data flow path ( Input and Output )

    Data flow path is nothing but it is flow of data from one task to another task. Data out from the first task will be taken as input of next task.

    It is possible to create the package with two different direction for data without error and data with error. Here Green color data flow path indicate the data without error, on the other hand Red Color indicate fail component redirection.

    6. Event Handlers

    7. Data Viewer

    Data viewer is much useful when create the package with multiple tasks, to see the data visually. It has different types. Here I am using data grid to see the out put data from flat source.

    8. Success and Fail

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s