Understanding the BIDS
As a beginner before starting the MS SQL Server 2008 SSIS package, I personally think that the understanding the Integration Services in Business Intelligence Development Studio (SSIS BIDS) is very important. So in this article I am trying to provide a general idea related to SSIS BIDS. Hope it will be interesting and helpful for beginners.
Tropics to discuss
Here I am trying to discuss about 2 most important tropics of BIDS
1. The Solution Explorer
2. SSIS Designer
The Solution Explorer
Please look at the bellow figure of solution explorer
We can open it by menu View à Solution Explorer or by Clicking [ Ctrl+Alt+L ] Shortcut menu. The solutions explorer contains several folders mentioned bellow.
1. Data Source Folder
It conations connection shared by multiple SSIS package of the same project. We can create connection manager in a package from the existing data source manager. A data source can be defined one time and then referenced by connection managers in multiple packages. If the data source and the packages that reference it reside in the same project, the connection string property of the data source references is automatically updated when the data source changes.
It conations connection shared by multiple SSIS package of the same project. We can create connection manager in a package from the existing data source manager. A data source can be defined one time and then referenced by connection managers in multiple packages. If the data source and the packages that reference it reside in the same project, the connection string property of the data source references is automatically updated when the data source changes.
2. Data Source View Folder
It contains the subset of the data in a data source and also contains named queries. We can designate tables, views, or named queries from a data source view as the source of data for a data flow source. It can be shared by multiple packages in a project.
3. SSIS Package Folder
A package is the unit of work in integration service. A single project has multiple packages. The extension of a package is dtsx.
4. Miscellaneous Folder
The folder contains others files that we want to add in our SSIS project. It may be document file, Image file etc.
The folder contains others files that we want to add in our SSIS project. It may be document file, Image file etc.
SSIS Designer
SSIS Designer is a graphical tool to design SSIS Package. It contains several Tabs. We have to understand each tab. Please look at the bellow figure of SSIS Designer.
1. Control flow Tabs
In this Tabs we can arrange the containers and tasks in the control flow tabs. It provides the logic for when data flow components are run and how they are run. Also control flows can: perform looping, calling stored procedures, moving files, managing error handling, check a condition and then call different tasks including data flows depending on the result, processing of SSAS cubes etc.
In this Tabs we can arrange the containers and tasks in the control flow tabs. It provides the logic for when data flow components are run and how they are run. Also control flows can: perform looping, calling stored procedures, moving files, managing error handling, check a condition and then call different tasks including data flows depending on the result, processing of SSAS cubes etc.
2. Data flow Tabs
On the Data Flow tab, you combine into a data flow sources that extract data, transformations that modify and aggregate data, destinations that load data, and paths that connect the outputs and inputs of data flow components.
A data flow defines a flow of data from a source to a destination. Data flows between our selected entities (sources, transformations, destinations).
On the Data Flow tab, you combine into a data flow sources that extract data, transformations that modify and aggregate data, destinations that load data, and paths that connect the outputs and inputs of data flow components.
A data flow defines a flow of data from a source to a destination. Data flows between our selected entities (sources, transformations, destinations).
3. Event Handlers Tabs
Here we can create workflow to responds on specified events of package. For an example we can create an event handler that sends an e-mail message when a task fails.
Here we can create workflow to responds on specified events of package. For an example we can create an event handler that sends an e-mail message when a task fails.
4. Package Explorer tab
This tab provides a convenient explorer view of the package, with the package as a container at the top of the hierarchy, and underneath it, the connections, executables, event handlers, log providers, precedence constraints, and variables that you have configured in the package.
This tab provides a convenient explorer view of the package, with the package as a container at the top of the hierarchy, and underneath it, the connections, executables, event handlers, log providers, precedence constraints, and variables that you have configured in the package.
5. Progress TabIt display the information about package Executions when we run a SSIS package in BI Studio. The Progress tab lists the start time, the finish time, and the elapsed time for validation and execution of the package and its executables; any information or warnings for the package; progress notifications; the success or failure of the package; and any error messages that are generated during package execution.
6. Connection Manager Area
Connection managers represent a layer of abstraction between the SSIS package runtime environment and the data sources. Connection managers contain the data source connection string and other related properties. At package execution time the connection managers manage the physical connectivity to data sources and destinations. Multiple tasks can share the same connection manager. In fact, multiple tasks can use the same connection to the database. On the other hand, we could also create a separate connection manager to force each task to use a dedicated connection.
Connection managers represent a layer of abstraction between the SSIS package runtime environment and the data sources. Connection managers contain the data source connection string and other related properties. At package execution time the connection managers manage the physical connectivity to data sources and destinations. Multiple tasks can share the same connection manager. In fact, multiple tasks can use the same connection to the database. On the other hand, we could also create a separate connection manager to force each task to use a dedicated connection.
References
http://msdn.microsoft.com/en-us/library/ms174181(v=sql.105).aspx
