constructed by provide structure in packages provide services to...

Constructed by Control Flow Elements

Provide structure in packages

Provide services to tasks

Common Tasks:

Data Flow task

Data tasks

Analysis Services task

File and Network Protocol Tasks

Scripting tasks

SQL Server tasks

Maintenance tasks

Task Definition

Data Flow task • Runs data flows to extract data, apply

column level transformations, and load data

Bulk Insert task • Efficiently copies large amounts of data

(text or BCP native format) into a SQL Server table or view

Execute SQL task • Runs SQL statements or stored procedures

from a package

Data Profiling task • Identifies potential problems with data

quality

Task Definition

Analysis Services Execute DDL task

• Runs data definition language (DDL) statements that can create, drop, or alter mining models and multidimensional objects such as cubes and dimensions

Analysis Services Processing task

• Processes Analysis Services objects such as cubes, dimensions, and mining models

Data Mining Query task

• Runs SQL statements or stored procedures from a package

Task Definition

File System task Creates, moves, or deletes directories and files

FTP task Performs FTP upload and download actions

Message Queue task

Allows messages to transmit between packages or to custom applications

Send Mail task Formats and sends an email message

Web Service task Executes a Web service method

XML task Retrieves, merges, validates, alters, and saves XML documents

Task Definition

Script task • Allows the execution of scripts for actions

not available in other tasks

Execute Process task

• Runs an application or batch file

ActiveX Script task

• Legacy ActiveX capability. Do not use for new development.

Precedence constraints determine a package’s execution flow

Precedence executable must run successfully for the constrained executable to run

Success

Precedence executable must not succeed for the constrained executable to run

Failure

Constrained executable runs after the precedence executable runs

Completion

Must be a valid Integration Services expression that evaluates to True or False

Can be combined with constraint values

Expression only

Expression and constraint value

Expression or constraint value

Task A

Task B

Task C

Success and Condition 1

Success and Condition 2

Multiple constraints can be combined with AND/OR logic

Containers provide a scope for defining variables

Containers are different from the grouping functionality

Containers provide scope for precedence constraints

Manage properties on multiple related tasks

Debugging by isolating actions

Provide a scope for variables

Sequence Container

Task C Task A Task B

For Loop

Property Purpose

InitExpression • Initializes the counter (@Counter=0)

EvalExpression • Defines condition for exiting loop

(@Counter > 5)

AssignExpression • Specifies how the counter is incremented

(@Counter = @Counter + 1)

Uses a variable as a counter

Task A

Task B Evaluate and increment variable

Foreach Enumerator

Use case

File Perform tasks for all files in a folder with the .txt extension

Item Enumerate the names of executables and working directories that an Execute Process task uses

ADO Collect all rows in an ADO recordset

AD.NET Enumerate the schema information about a data source, such as a list of tables

From Variable Enumerate an array, an ADO.NET DataTable, an Integration Services enumerator,etc

NodeList Enumerate the result set of an XML Path Language (XPath) expression

SMO Act on SQL Management Objects, such as getting a list of the views in a database