9\9 ssis 2008r2_training - package reliability and package execution

$: 9\9 SSIS 2008R2_Training - Package Reliability and Package Execution$
.

Presented by: Pramod Singla

[email protected]

Package Reliability Package Logging

◦ Configuring Logging◦ Configuring Logging in a Scipt Task and Viewing Logs

Event Handling◦ Demo: Adding a Task to an Event Handler

Transactions◦ Demo: Configuring Transaction Support

Checkpoints◦ Demo: Working with Checkpoints◦ Demo: Using Checkpoints with Containers

Package Execution Package Execution Utilities

◦ Demo: Using DTExec in the command line◦ Demo: Using DTExecUI

SQL Server Agent Jobs◦ Demo: Creating a SQL Server Agent Job

Summary

[email protected] (www.pramodsingla.wordpress.com)

Debugging Breakpoints

◦ Demo: Introducing Breakpoints◦ Demo: Working with Breakpoints

Script Task Debugging◦ Demo: Debugging a Script Task

Data Viewer◦ Demo: Using the Grid Viewer◦ Demo: Using the Histogram Viewer◦ Demo: Using the Scatterplot Viewer◦ Demo: Using the Chart ViewerRow Count Transformation


At run time, executables (packages and Foreach Loop, For Loop, Sequence, and task host containers) raise events

Event handlers can perform tasks such as the following:◦ Clean up temporary data storage when a package or task finishes

running.◦ Custom Logging ◦ Send an e-mail message when an error or a warning occurs or when a

task fails.


Transaction support is built in to SSIS. The TransactionOption property exists at the package level, container level

(e.g. For Loop, Foreach Loop, Sequence, etc.), as well as just about any Control Flow task (e.g. Execute SQL task, Data Flow task, etc.).

TransactionOption can be set to one of the following:◦ Required - if a transaction exists join it else start a new one◦ Supported - if a transaction exists join it (this is the default)◦ NotSupported - do not join an existing transaction

MSDTC Service must be running.

[email protected] (www.pramodsingla.wordpress.com)[email protected] (www.pramodsingla.wordpress.com)

Integration Services can restart failed packages from the point of failure, instead of rerunning the whole package.

Configuring Checkpoints◦ To implement checkpoints in your package, you must configure several properties at the

package level:◦ CheckpointFileName: Specifies the full path and filename of your checkpoint file.◦ CheckpointUsage - Determines if/how checkpoints are used. Choose from these

options: Never (default), IfExists, or Always. Never indicates that you are not using Checkpoints. IfExists is the typical setting and implements the restart at the point of failure behavior. If a Checkpoint file is found it is used to restore package variable values and restart at the point of failure. If a Checkpoint file is not found the package starts execution with the first task. The Always choice raises an error if the Checkpoint file does not exist

◦ SaveCheckpoints: Specifies whether the package saves checkpoints. Set to True to enable checkpoints on the package.

◦ by default, no tasks are setup to log checkpoints. For each task and container in the package that you want to identify as a restart point, you must set the FailPackageOnFailure property True.


To run an Integration Services package, you can use one of the following tools:◦ The dtexec command prompt utility (dtexec.exe).◦ The Execute Package Utility (dtexecui.exe).◦ A SQL Server Agent job.◦ Run in Business Intelligence Development Studio


The dtexec command prompt utility is used to configure and execute SQL Server Integration Services packages

dtexec /dts "MSDB\OnlineDemo\Precedence_Constraints“

dtexec/FILE "C:\Users\pram\Desktop\SSIStraining\temp\Precedence_Constraints.dtsx" /CHECKPOINTING OFF /REPORTING EW


GUI to execute SSIS package To launch Type “dtexecui” in RUN Double click on package will automatically

open it In package store right click a package and

run


You can automate and schedule the execution of SQL Server Integration Services packages by using SQL Server Agent


Package Reliability Package Logging

◦ Configuring Logging◦ Configuring Logging in a Scipt Task and Viewing Logs

Event Handling◦ Demo: Adding a Task to an Event Handler

Transactions◦ Demo: Configuring Transaction Support

Checkpoints◦ Demo: Working with Checkpoints◦ Demo: Using Checkpoints with Containers

Package Execution Package Execution Utilities

◦ Demo: Using DTExec in the command line◦ Demo: Using DTExecUI

SQL Server Agent Jobs◦ Demo: Creating a SQL Server Agent Job


Contact me : ◦ [email protected]◦ http://pramodsingla.wordpress.com/

Microsoft Resources:◦ http://consultingblogs.emc.com/jamiethomson/archive/2005/06/11/SSIS_3A00_-

Custom-Logging-Using-Event-Handlers.aspx◦ http://www.mssqltips.com/sqlservertip/1585/how-to-use-transactions-in-sql-server-

integration-services-ssis/


9\9 ssis 2008r2_training - package reliability and package execution

Data & Analytics