Page 201 - DCAP508_DATABASE_ADMINISTRATION

Unit 14: SQL Server Integration Services

Apart from the above discussed constraints, conditions can also be defined as an expression with Notes
precedence constraints that is evaluated at runtime; depending on its value the transition is
decided. In the image below, After Task A, Task B will be executed if the value of X >= Z or Task
C will be executed if the value of X < Z. Constraint and expression can also be combined in a
single condition with either AND or OR operator.

Task What are Precedence constraints? What is its role in a SSIS package?

Figure 14.4

Variables

The concept of a variable in SSIS is same as the variables in any other programming language.
It provides temporary storage for parameters whose values can change from one package
execution to another, accommodating package reusability. It is used to dynamically configure a
package at runtime. For example, to execute the same T-SQL statement or a script against a
different set of connections. Depending on the place where a variable has been defined, its scope
varies. Variables can be declared at package, container, task or handlers level.
In SSIS, there are two types of variables – System (predefined) variables whose values are set by
SSIS (ErrorCode, ErrorDescription, MachineName, PackageName, StartTime etc.) and cannot be
changed; User variables, created as required at the time of package development, can be assigned
a value of the corresponding type.

Note: An exception applies here, there is a system variable called “Propagate” whose value can
be changed from its default value TRUE to FALSE to stop event bubbling from a task to its parent
and grandparent. T
Connection Managers
A connection manager is a logical representation of a connection. SSIS provides different types
of connection managers which use different data providers and enable packages to connect to a
variety of data sources and servers.

A package can have multiple instances of connection managers and one connection manager can
be used by multiple tasks in the package.
Some examples of connection managers are:

ADO Connection Manager – Connects to ActiveX Data Objects (ADO) objects.
ADO.NET Connection Manager – Connects to a data source by using a .NET provider.
OLEDB Connection Manager – Connects to a data source by using an OLE DB provider.
Flat File Connection Manager – Connect to data in a single flat file.

FTP Connection Manager – Connect to an FTP server.

LOVELY PROFESSIONAL UNIVERSITY 195