Ssis2008 120710214348-phpapp02

SQL Server Integration Service 2008

What is SSIS?  SQL Server Integration Services (SSIS)- Microsoft Platform for developers to Extract, Transform and Load (ETL) packages along with other automation tasks.  SSIS contains graphical tools and wizards for building and debugging packages( consistent with Visual Studio IDE).  SSIS is a replaces of Data Transformation Services (DTS).

SSIS Tools  Wizards  SQL Server Import and Export Wizard  Package Migration Wizard  …  SSIS Designer  built into the BI Development Studio and is the main surface for package development.  Command-prompt utilities  Dtexec (run an existing package at the command prompt )  Dtutil (manage existing packages at the command prompt)

SSIS Development Environment  SSIS Projects:  Data Sources  Data Source Views  SSIS Packages:  Control flow  Tasks  Loops, sequences and events  Variables and scoping  Precedence constraints  Connections Manager  Data flow  Source and destination adapters  Transformations  Multiple sources with joins and unions  Multiple destinations with splits and multicast

Packages  Collection of tasks which are executed in an orderly fashion by the run time engine.  It is a unit of work which is Retrieved, executed and saved.  Its an XML file , which can be either saved in Sql Server or in an file system.  Can be executed by –  SQL Server Agent  DTEXEC Or DTEXECUTil (Command line utility bundled with SSIS to execute a package, another similar one is DTEXECUI which has the GUI).

Control Flow and Tasks  Main Workflow of the package.  Dictates the sequence of execution of the package.  SSIS provides three types of control flow elements:  Task is a processing unit responsible for performing a designated step, such as running T-SQL Command, launching an external process, handling a file system operation and so on.  Containers groups and organize tasks logically.  Precedence constraints connect containers or tasks into one control flow depending on the outcome of an upstream task.

Precedence Constraints Success Failure Complete

Connection Managers  SSIS uses connection managers to integrate different data sources into packages.

Data Flow  The most important task for moving the data from source to destination.  It is a buffer oriented Architecture.  Created by using different types of data flow elements including:  Sources that extract data.  Transformations that modify data.  Destinations that load data.  Data Paths that connect the outputs and inputs of data flow components into a data flow.

Event Handling and Logging  During runtime, events are raised by the containers and tasks.  Event Handlers perform following tasks:  Clean up temporary data storage when a package or task finishes.  Refresh data in a table when a lookup reference table fails.  Send an email when an error or warning occurs.

Logging  SQL Server Integration Services includes log providers that you can use to implement logging in packages, containers, and tasks.  Logging captures runtime information about the package.  Log Providers –  Text File Log Provider.  SQL Server Profiler Log Provider.  SQL Server Log Provider.  XML File log Provider.  Windows Event Log Provider.

Contd..  Elements in the log Schema:  Computer- The name of the computer on which the log event occurred.  Operator - The identity of the user who launched the package  SourceName -The name of the container or task in which the log event occurred  SourceID - The unique identifier of the package; the For Loop, Foreach Loop, or Sequence container; or the task in which the log event occurred.  ExecutionID - The GUID of the package execution instance.  MessageText - A message associated with the log entry.  Data Bytes- A byte array specific to the log entry. The meaning of this field varies by log entry.

Transactions  SSIS Container types can be configured to use transaction.  Three Options for configuring transactions:  Supported – joins any transaction started by the parent container.  Not Supported – does not join or start a transaction.  Required – Starts a transaction, unless one is already started by the parent. If transaction exists, it joins the existing one.

Checkpoints  SSIS can restart failed packages from the point where it stopped, instead of running the whole package.  The information about the package execution is written in a checkpoint file.  Checkpoint file restarts the package from the point of failure.  A package can be restarted only at the Control Flow Level. A package cannot be restarted in the middle of the data flow.

Configuring a package to restart The following table lists the package properties that you set to implement checkpoints. The CheckpointUsage property can be set to the following values:  Never  Always  IfExists

Variables  SSIS supports two types of variables: system and user-defined.  User-defined variables can be used in many ways in SSIS: in scripts; in the expressions used by precedence constraints, the For Loop container, the Derived Column transformation, and the Conditional Split transformation; and in the property expressions that update property values.

Usage Scenarios  Property Expressions  Data Flow expressions  Precedence constraint Expressions  Parameters and Return codes  For Loop  Package Configuration  Script tasks

Package Debugging  Control flow  Breakpoints  Debug window  Watch window  Data flow  Data viewer  Row counts

Deploying the packages  SSIS contains a feature called Package Deployment Utility which allows to assemble the SSIS packages, configurations and supporting files to deployment folder and builds an executable file.

Ssis2008 120710214348-phpapp02

More Related Content

What's hot

Similar to Ssis2008 120710214348-phpapp02

Recently uploaded

Ssis2008 120710214348-phpapp02