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)
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
Select the File -> New -> Project … New project dialog box will be opened with available templates
Select ‘Business Intelligence Project’ under ‘project type’, then select Integration services project’ from left side template pane.
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