Page 202 - DCAP508_DATABASE_ADMINISTRATION

Database Administration

Notes By default, every task that uses a connection manager during execution opens a connection,
performs the operation and closes the connection before moving to the next task. Each task has
its own connection. Consider a scenario where there are three tasks in package and they use the
same connection manager; during runtime there would be three connections open and closed at
the source. However, ideally all three tasks would be executed in a single connection; so that
only one connection is open to the source irrespective of how many tasks use the connection.
The RetainSameConnection property on the OLE DB Connection Manager enables multiple
tasks to run in a single connection if the RetainSameConnection property equals TRUE.

Figure 14.5

Self Assessment

State true or false:

6. A package can have multiple instances of connection managers and one connection manager
can be used by multiple tasks in the package.
7. In SSIS, there are three types of variables.

8. Precedence constraints link the items in a package into a logical flow and specify the
conditions upon which the items are executed.
9. There are two types of containers in SSIS.

10. A package is a collection of tasks which are executed in an orderly fashion by SSIS runtime
engine.

14.6 Summary

SSIS provides a way to build packages made up of tasks that can move data around from
place to place and alter it on the way.

SSIS is basically an ETL (Extraction, Transformation, and Load) tool whose main purpose
is to do extraction, transformation and loading of data but it can be used for several other
purposes for example, to automate maintenance of SQL Server databases, update
multidimensional cube data etc., as well.

196 LOVELY PROFESSIONAL UNIVERSITY