Basics Of SSIS
SQL Server Integration Services
SQL Server Integration Services (SSIS) is a platform for building
high performance data integration solutions, including extraction, transformation,
and load (ETL) packages for data warehousing. A simpler way to think of SSIS is
that it’s the solution for automating data movements. 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.
Creating a Package
The Import and Export Wizard is easy to use, but it only taps a small part of the
functionality of SSIS. To really appreciate the full power of SSIS, you’ll need to use
BIDS to build an SSIS package. A package is a collection of SSIS objects including:-
• Connections to data sources.
• Data flows, which include the sources and destinations that extract and load
data, the transformations that modify and extend data, and the paths that link
sources, transformations, and destinations.
• A Control flow, which includes tasks and containers that execute when the
package runs. You can organize tasks in sequences and in loops.
• Event handlers, which are workflows that runs in response to the events raised
by a package, task, or container.
Working with Connection Managers
SSIS uses connection managers to integrate different data sources into packages.
SSIS includes a wide variety of different connection managers that allow you to
move data around from place to place.
To create a Connection Manager, you right-click anywhere in the Connection
Managers area of a package in BIDS and choose the appropriate shortcut from the
shortcut menu. Each Connection Manager has its own custom configuration dialog
box with specific options that you need to fill out.
Building Control Flows
The Control Flow tab of the Package Designer is where you tell SSIS what the
package will do. You create your control flow by dragging and dropping items from
the toolbox to the surface, and then dragging and dropping connections between the
objects. The objects you can drop here break up into four different groups:
• Tasks are things that SSIS can do, such as execute SQL statements or transfer
objects from one SQL Server to another.
• Maintenance Plan tasks are a special group of tasks that handle jobs such as
checking database integrity and rebuilding indexes.
• The Data Flow Task is a general purpose task for ETL (extract, transform, and
load) operations on data. There’s a separate design tab for building the details
of a Data Flow Task.
• Containers are objects that can hold a group of tasks.
Building Data Flows
The Data Flow tab of the Package Designer is where you specify the details of any
Data Flow tasks that you’ve added on the Control Flow tab. Data Flows are made up
of various objects that you drag and drop from the Toolbox:
• Data Flow Sources are ways that data gets into the system.
• Data Flow Transformations let you alter and manipulate the data in various
ways.
• Data Flow Destinations are the places that you can send the transformed data.
Table.
machine, the Excel source and destination will throw an exception.
During development, you can select Project Project_name
Properties, select the Debugging page and change the
Run64BitRuntime property to false. When deploying the package,
you’ll need to shell out to the 32-bit SSIS runtime when scheduling
the package.
Creating Event Handlers
SSIS packages also support a complete event system. You can attach event handlers
to a variety of events for the package itself or for the individual tasks within a
package. Events within a package “bubble up.” That is, suppose an error occurs
within a task inside of a package. If you’ve defined an OnError event handler for
the task, then that event handler is called. Otherwise, an OnError event handler for
the package itself is called. If no event handler is defined for the package either, the
event is ignored.
Event handlers are defined on the Event Handlers tab of the Package Designer.
When you create an event handler, you handle the event by building an entire
secondary SSIS package, and you have access to the full complement of data flows,
control flows, and event handlers to deal with the original event.
OnError event, you can notify operators by e-mail if anything
goes wrong in the course of running an SSIS package.
Package Running Utility :-
SQL Server also includes a command-line utility, dtexec, that lets
you run packages from batch files.
SQL Server Integration Services (SSIS) is a platform for building
high performance data integration solutions, including extraction, transformation,
and load (ETL) packages for data warehousing. A simpler way to think of SSIS is
that it’s the solution for automating data movements. 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.
Creating a Package
The Import and Export Wizard is easy to use, but it only taps a small part of the
functionality of SSIS. To really appreciate the full power of SSIS, you’ll need to use
BIDS to build an SSIS package. A package is a collection of SSIS objects including:-
• Connections to data sources.
• Data flows, which include the sources and destinations that extract and load
data, the transformations that modify and extend data, and the paths that link
sources, transformations, and destinations.
• A Control flow, which includes tasks and containers that execute when the
package runs. You can organize tasks in sequences and in loops.
• Event handlers, which are workflows that runs in response to the events raised
by a package, task, or container.
Working with Connection Managers
SSIS uses connection managers to integrate different data sources into packages.
SSIS includes a wide variety of different connection managers that allow you to
move data around from place to place.
To create a Connection Manager, you right-click anywhere in the Connection
Managers area of a package in BIDS and choose the appropriate shortcut from the
shortcut menu. Each Connection Manager has its own custom configuration dialog
box with specific options that you need to fill out.
Building Control Flows
The Control Flow tab of the Package Designer is where you tell SSIS what the
package will do. You create your control flow by dragging and dropping items from
the toolbox to the surface, and then dragging and dropping connections between the
objects. The objects you can drop here break up into four different groups:
• Tasks are things that SSIS can do, such as execute SQL statements or transfer
objects from one SQL Server to another.
• Maintenance Plan tasks are a special group of tasks that handle jobs such as
checking database integrity and rebuilding indexes.
• The Data Flow Task is a general purpose task for ETL (extract, transform, and
load) operations on data. There’s a separate design tab for building the details
of a Data Flow Task.
• Containers are objects that can hold a group of tasks.
Building Data Flows
The Data Flow tab of the Package Designer is where you specify the details of any
Data Flow tasks that you’ve added on the Control Flow tab. Data Flows are made up
of various objects that you drag and drop from the Toolbox:
• Data Flow Sources are ways that data gets into the system.
• Data Flow Transformations let you alter and manipulate the data in various
ways.
• Data Flow Destinations are the places that you can send the transformed data.
Table.
Tip :-
If you are running SQL Server Integration Services on a 64-bitmachine, the Excel source and destination will throw an exception.
During development, you can select Project Project_name
Properties, select the Debugging page and change the
Run64BitRuntime property to false. When deploying the package,
you’ll need to shell out to the 32-bit SSIS runtime when scheduling
the package.
Creating Event Handlers
SSIS packages also support a complete event system. You can attach event handlers
to a variety of events for the package itself or for the individual tasks within a
package. Events within a package “bubble up.” That is, suppose an error occurs
within a task inside of a package. If you’ve defined an OnError event handler for
the task, then that event handler is called. Otherwise, an OnError event handler for
the package itself is called. If no event handler is defined for the package either, the
event is ignored.
Event handlers are defined on the Event Handlers tab of the Package Designer.
When you create an event handler, you handle the event by building an entire
secondary SSIS package, and you have access to the full complement of data flows,
control flows, and event handlers to deal with the original event.
Tip :-
By adding event handlers that call the Send Mail task to theOnError event, you can notify operators by e-mail if anything
goes wrong in the course of running an SSIS package.
Package Running Utility :-
SQL Server also includes a command-line utility, dtexec, that lets
you run packages from batch files.
Comments
Post a Comment