SSIS

How to implement checkpoints in packages

Integration Services supports checkpoints. You can configure a package to use checkpoints and this way make it is possible to restart it from the point of failure, rather than rerun the whole package. If a package is configured to use checkpoints, information about package execution is written to a checkpoint file. When you rerun the failed package, the checkpoint file is used to identify where the package failed previously and restart the package from the point of failure. If the package reruns successfully, the checkpoint file is deleted.

To set the properties related to checkpoints, expand the Checkpoints node in the Properties window.

To configure checkpoints, follow these steps:

1. Set the SaveCheckpoints property to True to indicate that the package saves checkpoints.
2. Provide the name and location of the checkpoint file in CheckpointFileName property.
3. Set the value of the CheckpointUsage property (see Table 16-2).
4. To identify tasks and containers in the package as restart points, select each task or container, and in the Properties window set its FailPackageOnFailure property to True.

Table 16-2The Possible Values of the CheckpointUsage Property

Value	Behavior
Never	Indicates that the checkpoint file is not used.
Always	Indicates that the checkpoint file is always used. If the checkpoint file is not found, the package fails.
IfExists	Indicates that the checkpoint file is used if it exists. If the checkpoint file is not found, the whole package is rerun.

How to set user properties to secure packages

Integration Services provides a variety of package protection levels to encrypt packages or to omit saving sensitive information in the package definition. When you develop a package in Business Intelligence Development Studio, you set the package protection to a level that is suitable for a single developer of a team of developers. Some package protection levels (EncryptAllWithUserKey and EncryptSensitiveWithUserKey) encrypt packages using a key based on the user profile. This means that only the same user using the same profile can load and work with the package. Obviously, these protection levels are difficult to use in a team environment because developers cannot open packages that were created by other team members. Other protection levels (EncryptAllWithPassword and EncryptSensitiveWithPassword) require that the passwords be known to all developers. Frequently the DontSaveSensitive protection level is used during package development. You can set the protection level to ServerStorage to rely only on the built-in security features of SQL Server to protect packages.

To set the properties related to package protection level expand the Security node in the Properties window.

To configure the protection level,

1. Set the ProtectionLevel property.
2. If applicable, provide a password in the PackagePassword property.

If you import or export a package in SQL Server Management Studio or save a copy of a package in Business Intelligence Development Studio, you can change the protection level that the package uses.

How to implement looping in packages

In DTS, it was difficult to implement looping in packages. The solution typically included custom code. In SQL Server 2005, Integration Services introduces two new container types, the Foreach Loop and the For Loop, that make it very easy to include looping in the package control flow.

1. For Loop repeats its control flow until a specified expression evaluates to False.
2. The Foreach Loop repeats the control flow for each member in the collection of a specified enumerator type.

Of the two types of loops, the For Loop is probably the most straightforward to implement. To configure it, you provide the expression the evaluation results of which determine whether the loop repeats, and optionally, expressions to initialize the loop counter and increment or decrement the loop counter. The three properties to configure the For Loop are: EvalExpression (the evaluation expression), InitExpression (the initialization expression), and AssignExpression (the expression to increment or decrement the loop counter).

In the following very simple implementation, you use a variable, varCounter, to control loop execution. The variable must have a numeric data type. The loop repeats the loop repeats 5 times.

Set InitExpression to @varCounter = 1 Set EvalExpression to @varCounter < 6 Set AssignExpression to @varCounter = @varCounter +1

To use the Foreach Loop to implement looping you first choose the type of enumerator to use. You select the enumerator type on the Collection page of the Fore each Loop Editor dialog box (see Figure 16-34).

Figure 16-34The Collection page.

The built-in enumerator types that Integration Services provides (see Table 16-3) support repeating control flows to enumerate a wide variety of objects and items. For example, you can use the Foreach File enumerator to repeat a control flow for all the files in a specified folder, regardless of the number of files present in the folder. You will learn that the Foreach File enumerator supports wildcards, which makes it possible to filter the filesand choose just those that you wish to enumerate.

Table 16-3The Enumerator Types to Use with the Foreach Loop

Enumerator Type	Use
Foreach ADO Enumerator	Enumerate rows in an ADO recordset.
Foreach ADO.NET Schema Rowset Enumerator.	Enumerate the schema information of a data source.
Foreach ADO.NET Schema Rowset Enumerator.	Enumerate the schema information of a data source.
Foreach File Enumerator.	Enumerate files in a folder and subfolders.
Foreach From Variable Enumerator	Enumerate the enumerable object in a specified variable.
Foreach Item Enumerator	Enumerate items that are collections.
Foreach Nodelist Enumerator	Enumerate the resultset of an XML Path Language (XPath) expression.
Foreach SMO Enumerator	Enumerate SQL Server Management Objects (SMO) objects.

After you select the enumerator type to use and configure it, you need to map variables to the collection value. This is how the Foreach Loop provides information to the repeating control flow. Let's walk through a couple of scenarios.

 

 

 

How to format package layout

As you develop a package and modify control and data flows by adding and deleting items, you might find that the package layout becomes more difficult to understand. In short, the graphical layout does not communicate package functionality in the most optimal way. You could work your way through the layout, item by item, resizing and re-placing each item on the design surface, but Business Intelligence Studio offers a much easier way to do this.

On the Format menu (see Figure 16-37), you will find useful options to size and align items and to set the horizontal and vertical spacing between items.

 

 

 

How to use constraints and expressions to determine whether executables run

Precedence constraints link executables (containers and tasks) in packages into a control flow and specify conditions that determine whether executables run. A precedence constraint connects two executables: the precedence executable and the constrained executable.

You can use expressions in place of or in combination with the outcome (success, failure, or completion) of the precedence executable to determine whether the constrained executable runs. The Precedence Constraint Editor is shown in.

Figure 16-38The Precedence Constraint Editor shows the available evaluation operations.

Select either Expression and Constraint or Expression or Constraint to use an expression in the evaluation operation of the precedence constraint. The expression that you use must evaluate to a Boolean. For example, a package could use an expression that evaluates whether the constrained executable runs based on the amount of disk space available, the number of rows a data flow inserted into a table, or the day of the month.

Let's look at some sample expressions. In the following expression, the variable varRowCount, set by a Row Count transformation in a data flow, is compared to the value 1000. You could use this expression to determine whether the package should run a task that backs up a database depending on the number of rows inserted into the destination database. The prefix @ is required when variables are used in expressions to differentiate the variables from column names. In most user interface, the @ is added for you automatically.

@varRowCount > 1000

Likewise, in the following expression, the variable @varDisk, set by a Script task, is compared to the value 20. You could use this expression to determine whether to continue package execution.

@varDisk >= 20

In the following expression, the GETDATE function determines the day. If the day is the first day in the month, the constrained executable runs.

DATEPART("day",GETDATE()) == 1

 

 

Event handlers in SSIS 2012 or 2008 Integration Services

1. In this article i will explain the event handlers in SSIS 2012
2. Event handlers are a component that execute tasks based on an event that occurs at the package, container or task level at run time.
3. The tasks that can be performed within an event handler are the same tasks that are available within the control flow.
4. The events that can be defined include:

 

• OnError. This event is raised by an executable when an error occurs.
• OnExecStatusChanged. This event is raised by an executable when its execution status changes.
• OnInformation. This event is raised during the validation and execution of an executable to report information. This event conveys information only, no errors or warnings.
• OnPostExecute. This event is raised by an executable immediately after it has finished running.
• OnPostValidate. This event is raised by an executable when its validation is finished.
• OnPreExecute. This event is raised by an executable immediately before it runs.
• OnPreValidate. This event is raised by an executable when its validation starts.
• OnProgress. This event is raised by an executable when measurable progress is made by the executable.
• OnQueryCancel. This event is raised by an executable to determine whether it should stop running.
• OnTaskFailed. This event is raised by a task when it fails.
• OnVariableValueChanged. This event is raised by an executable when the value of a variable changes. The event is raised by the executable on which the variable is defined.
• OnWarning. This event is raised by an executable when a warning occurs.

Event handlers can perform the following tasks:

• Clean up temporary data storage when a package or task finishes running.
• Retrieve system information to assess resource availability before a package runs.
• Refresh data in a table when a lookup in a reference table fails.
• Send an e-mail message when an error or a warning occurs or when a task fails.

For example,

An OnError event is raised when an error occurs. You can create custom event handlers for these events to extend package functionality and make packages easier to manage at run time.

 

 

 

 

 

 

Control flow Componets in SSIS 2012 or 2008 Integration services

1. In this article i will be explaining you the control flow components in ssis 2012
2. Control flow is responsible for managing how the entire package operates
3. There are three core components that can be used in the control flow to manage a package execution:

Tasks.

1. Tasks are commonly used to add functionality to a package.
2. This functionality is wide and varied and provides the ability to perform the work in the package.
3. The categories of tasks that are performed are as follows:

• Data tasks work with data.
• File and network tasks interact with the file system and FTP sites.
• Scripting tasks add custom transformations and logic to SSIS packages.
• Database object transfer tasks manage the movement of SQL Server objects.
• Package execution task control the execution of SSIS and Data Transformation Services (DTS) packages.
• Analysis Services tasks interact with Analysis Services.
• Maintenance tasks perform common SQL administrative functions.
• Windows Management Instrumentation (WMI) tasks interact with WMI.

Precedence constraint.

Precedence constraints are used to control the workflow between the tasks within a package that will dictate what will proceed a task based on a failure, completion or successful execution of a previous task. You can also use expressions to control the workflow.

Containers.

Containers are used to group tasks together as one unit so that you can further refine the structure of the workflow within a package.