Control Flow
Control Flow contains a group of tasks to achieve start to end. Without having a Control Flow, no package on SSIS can be made. It also determines under what conditions they're executed.
Data Flow Task
A task where data flows (what ETL does). A Data Flow Task has to be a part of a Control Flow. One control flow can have a number of Data Flow Tasks in it. Each Data Flow Task has to have a Source, may or may not have a Transform/Destination. But at least a transform or Destination followed by the Source. Source is used to extract the data, Transform is used to transform the data while Destination is used to Load the data to a destination.
Precedence Constraints
Precedence Constraints link the individual executables together and determine how the workflow moves from one executable. The precedence constraints are the green, red and blue arrows (both solid and dotted) that connect the tasks and container to each other. Fig 1.
Send Email Task
Execute Process Task
Process-> Executable -> C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe
Process-> Arguments -> AdventureWorks.dbo.Employees out C:\emp_2012-07-30.txt -c -T -t,
Expressions-> Arguments -> "AdventureWorks.dbo.Employees out C:\\emp_" + SUBSTRING( (DT_STR,50,1252) GETDATE(), 1, 10 ) + ".txt -c -T -t,"
Defining Workflow by Value
Set the Value property on the precedence constraint, by default value is Success. The property supports three options:
Evaluation operation
Two other important configuration options in a precedence constraint are the Logical OR and Logical AND settings. These settings apply only to constrained executables and only if those executables have more than one precedence constraint directed to it.
For example, in the above figure, 2 dotted green line precedence constraints.
Breakpoint
Break SSIS packages in the execution when specific events occur in the Control Flow. There are ten events in the Control Flow: OnPreExecute, OnPostExecute, OnError, OnWarning, OnInformation, OnTaskFailed, OnProgress, OnQueryCancel, OnVariableValueChanged and OnCustomEvent.
Right-clicking on a task (E.g. Execute SQL task - Count employees) in the Control Flow, to select the option Edit Breakpoints.
Data Viewer
Data Viewers are containers that can contain a single SSIS buffer’s work. As the data passes through the Data Path you are able to view what is in each buffer. As each buffer passes into the Data Viewer, the processing of the SSIS package on that Data Path is paused. There are four types of data viewers. These are: Grid, Histogram, Scatter plot and Chart format. Go into the Data Flow (Fig 1, right side) and select the Data Path (Arrow 290 rows) that you want to pause the data processing.
Variables
Right-clicking in the Control Flow, to select the option Variables, Variables tool window will be display. Using the Variables widow, to mange custom variables.
Control Flow contains a group of tasks to achieve start to end. Without having a Control Flow, no package on SSIS can be made. It also determines under what conditions they're executed.
Data Flow Task
A task where data flows (what ETL does). A Data Flow Task has to be a part of a Control Flow. One control flow can have a number of Data Flow Tasks in it. Each Data Flow Task has to have a Source, may or may not have a Transform/Destination. But at least a transform or Destination followed by the Source. Source is used to extract the data, Transform is used to transform the data while Destination is used to Load the data to a destination.
Precedence Constraints
Precedence Constraints link the individual executables together and determine how the workflow moves from one executable. The precedence constraints are the green, red and blue arrows (both solid and dotted) that connect the tasks and container to each other. Fig 1.
Send Email Task
Execute Process Task
Process-> Executable -> C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe
Process-> Arguments -> AdventureWorks.dbo.Employees out C:\emp_2012-07-30.txt -c -T -t,
Expressions-> Arguments -> "AdventureWorks.dbo.Employees out C:\\emp_" + SUBSTRING( (DT_STR,50,1252) GETDATE(), 1, 10 ) + ".txt -c -T -t,"
Defining Workflow by Value
Set the Value property on the precedence constraint, by default value is Success. The property supports three options:
- Success: The precedence executable must run successfully for the constrained executable to run. This is the default value. The precedence constraint is set to green when the Success option is selected. (Send Mail task - 1)
- Failure: The precedence executable must fail for the constrained executable to run. The precedence constraint is set to red when the Failure option is selected.(Send Mail task - 2)
- Completion: The constrained executable will run after the precedence executable runs, whether the precedence executable runs successfully or whether it fails. The precedence constraint is set to blue when the Completion option is selected.
Evaluation operation
- Constraint: The option selected in the Value property. (Send Mail task - 1, & 2)
- Expression: If the expression is true, the workflow continues and executes the task. If the expression evaluates to false, the constrained executable does not run.
- Expression and Constraint: Both the Constraint Value property and the expression must evaluate to true for the constrained executable to run.
- Expression or Constraint: Either the Constraint Value property or the expression must evaluate to true for the constrained executable to run.
Two other important configuration options in a precedence constraint are the Logical OR and Logical AND settings. These settings apply only to constrained executables and only if those executables have more than one precedence constraint directed to it.
For example, in the above figure, 2 dotted green line precedence constraints.
Breakpoint
Break SSIS packages in the execution when specific events occur in the Control Flow. There are ten events in the Control Flow: OnPreExecute, OnPostExecute, OnError, OnWarning, OnInformation, OnTaskFailed, OnProgress, OnQueryCancel, OnVariableValueChanged and OnCustomEvent.
Right-clicking on a task (E.g. Execute SQL task - Count employees) in the Control Flow, to select the option Edit Breakpoints.
Data Viewer
Data Viewers are containers that can contain a single SSIS buffer’s work. As the data passes through the Data Path you are able to view what is in each buffer. As each buffer passes into the Data Viewer, the processing of the SSIS package on that Data Path is paused. There are four types of data viewers. These are: Grid, Histogram, Scatter plot and Chart format. Go into the Data Flow (Fig 1, right side) and select the Data Path (Arrow 290 rows) that you want to pause the data processing.
Variables
Right-clicking in the Control Flow, to select the option Variables, Variables tool window will be display. Using the Variables widow, to mange custom variables.
No comments:
Post a Comment