SSIS (SQL Server Integration Service) Control Flow Overview

Monday, December 14, 2009

Overview
The control flow in a SQL Server 2000 Integration Services (SSIS) package is constructed by using different types of control flow elements.
• Container
• Task
• Precedence constraints

Precedence Constraints

• Precedence constraints connect container and task in packages into an order control flow.

• You can control the sequence execution for tasks and containers, and specify conditions that determine whether tasks and containers run.

• Precedence constraint links two executables:

1 –The precedence executable and

2- The constrained executable.

• The precedence executable runs before the constrained executable and the execution result of the precedence executable may determine whether the constrained executable runs.

Execute SQL Tasks

The Execute SQL task runs SQL statements or stored procedures form a package. Execute SQL task can be used for the following purpose.

• Truncate a table or view in preparations for inserting data.

• Create, alter and drop database objects such as tables and views.

• Re-create fact and dimension tale before loading them.

• Run stored procedures.

Bulk Insert Task

The Bulk Insert task provides the quickest way to copy large amounts of data into a SQL server table or view.You can configure the Bulk Insert task in the following ways.

• Specify the OLE DB connection manager to connect to the destination SQL server database and table or view into which data is inserted.

• Specify the File or Flat File connection manager to access the source file.

• Define the format used by the Bulk Insert task

File System Task

The File System task performs operations on files and directories in the file system.

• All File System task operations use a source, which can be a file or a directory.

• The operations that copy and move file and directories and rename file use destination and a source.

SSIS Tools and Utilities

SSIS provides the following tools and utilities:
• Integration Services and Studio Environments= Describe how to use Business intelligence development studio and SQL server management studio with integration services.
•SSIS Designer = Graphical tool for building complex packages that include control flows, data flows, and event-driven logic.
•Integration Services Wizards= These wizards are for importing and exporting data, installing packages, migrating packages and creating package configurations.
•Command prompt utilities (SSIS) = For managing and running packages.
• Query Builder (SSIS)= Graphical tool for building queries.
• Expression Builder (SSIS) = Graphical tool for building expressions.

Overview of SOAP

• SOAP (Simple Object Access Protocol) is communication protocol which communicates between applications using XML format over internet.
• SOAP is platform and language independent. It is based on XML.
• SOAP is simple and extensible and it allows passing around firewalls.
• A SOAP message is an ordinary XML document containing the following elements.
1. Envelope (Required) = this element identifies XML document as a SOAP message.
2. Header (Optional) = It contains header information.
3. Body (Required) = It contains call and response information.
4. Fault (Optional) = It provides information about errors that occurred while processing the message.

Overview of SSIS Package

• The SSIS package is the unit of work that is retrieved, executed and saved.
• Package is an organized collection of connections, control flow elements, data flow elements, event handlers, variables and configurations.
• SSIS includes SSIS Designer and SQL Server Import and Export Wizard, in addition to the SSIS object model for creating packages.
• A package typically includes at least one connection manager.
  

Overview of SQL Server Integration Service

Monday, December 7, 2009

What is SSIS
- SSIS is a platform for building high performance data integration and workflow solutions, including the extraction, transformation and load (ETL) operations of data warehousing.
Uses of SSIS
- Populating Data Warehouses and Data Marts
- Merging Data from Heterogeneous Data Stores
- Cleaning and Standardizing Data
- Building Business Intelligence into a Data Transformation Process
- Automating Administrative Functions and Data Loading
SSIS Architecture
- The new integration services architecture is divided into two main sections.

1- Data Transformation pipeline (DTP). - Take the place of the old DTS data pump that was used in SQL server 7 and 200. - Primary function is handle the data flow between the source and target destinations

2 – Data Transformation Runtime (DTR) - A job execution environment that controls the control flow that is used in an SSIS package.

Data Transformation Pipeline (DTP): Overview The DTP takes care of the data flow and transformations that take place as rows are moved between the data source and data target - Each of these components exposes its own distinct object model

DTP uses data adapters to connect to the source and destination data sources.
The DTP engine is accessed using the DTP Pipeline object model.
Data Transformation Runtime (DTR): Overview
The DTR is essentially a job execution environment that controls the control flow that’s used in an SSIS package.

The DTR is accessed using the DTR object framework.
The DTR run-time object framework is the API that supports the integration service import/export wizard and the integration service designer in addition to the command-line dtexe tool.