SSIS Funda’s ……………………

Objective : Extraction of data, Transformation and insertion.
Control Flow: Roughly we can call it as a work flow.
Data Flows are the modules which does a series of operation to achieve a desired result.

SSIS 2005 provides three different types of control flow elements
1. Containers: Provide structures in packages.
2. Tasks: provides functionality.
3. Precedence constraint: it connects the executables, containers and tasks into an ordered flow.

First of all the package control flow must include a data flow task then only we can add a data flow to a package. For each Data flow task in package a separate instance of the data flow engine is invoked.
ERROR Handling in SSIS:

Typically three sort of errors can happen in SSIS viz. Data conversion errors, Expression evaluation errors or Look up Errors.
In the transformation editor, we can click configure Error Output to get the configure error o/p wizard.

There are three options:
1) Ignore Failure
2) Redirect Row
3) Fail Component

Other than these we have event handlers where we can execute some task or run scripts or do anything we want in case of error occurred or after execution of some block.

LOGGING in SSIS:
We can write log entries to text files, SQL Server profiler, Windows event log or xml files. Logs are associated with the packages and are configured at the package level. Each task or container in a package can log information into any package log. To enable logging in a package:
1. Open the integration services in BIDS.
2. On the SSIS menu click logging.
3. Select a log provider in the provider type list and then click ADD.
Deployment of SSIS packages:

SSIS supports the deployment of packages through the use of the package configurations and the ability to easily deploy packages using the package deployment utility.
1. Build the integration services project to create a package deployment utility.
2. Now run the package installer wizard to install the package.
VARIABLES: variables store values that a SSIS package and its contains, tasks and event handlers can use at run time.

SSIS provides a checkpoint facility which allows a package to restart at the point of failure.
SSIS connection managers:
1. OLE DB Connection.
2. Flat file connection.
3. ADO.Net connection
4. Analysis service connection.
Difference between DTS and SSIS:
1. No Deployment wizard in DTS.
2. Limited set of transformations in DTS.
3. No BI functionality in DTS.
4. Dynamic properties + ActiveX scripts in DTS, Configuration + Property Expression in SSIS
5. Message Boxes in active scripts, Message Boxes in script tasks.
Merge transformation can accept only two inputs where as Union ALL can take more than two inputs.
Data has to be sorted before merge transformation where as UNION ALL doesn’t have any such condition.