Integration Services provides a very valuable and
feature-rich extensible model, which is useful when the tasks and
components that come with Integration Services do not supply the
functionality you need. Most of the extensibility is achieved by
allowing you to write your own logic in scripts, custom tasks, and
custom components.
Script Tasks
A Script Task is a very
simple way to introduce custom code into the execution of a package. It
does not require building separate components—the code for the Script
Task is stored inside the package and compiled on demand before
execution. After you add a Script Task to the control flow of your
package, you can immediately edit it and start entering the code. When
you execute the package in SQL Server Business Intelligence Development
Studio, you can set breakpoints in your script code and debug the code
interactively.
Performance of Script
Tasks is very good because unlike the ActiveX Task in previous versions
of SQL Server, the Script Task actually compiles the scripts. The source
code is compiled to Microsoft intermediate language (MSIL), which is
then translated to native code during execution by the common language
runtime.
The advantage of the
Script Task is ease of use. The disadvantage is that the source code is
in the package and therefore can be seen by anyone who can edit it
(although sometimes this can be an advantage). Figure 1 shows the Script Task development environment.
Custom Tasks
If you want to
create a reusable task component that will be distributed as a compiled
assembly, you can create a custom task project. To build a custom task,
create a Class Library project in Visual Studio. Add a reference to the Microsoft.SqlServer.ManagedDTS
assembly. In the project properties, go to the Signing tab and select
the Sign The Assembly check box. Use a key file you already have or
create a key file by clicking New.
Next, create a class derived from Task and add the DtsTask
attribute to the class. Build the project, and add the compiled
assembly to the global assembly cache (GAC). Also copy the assembly to
%ProgramFiles%\Microsoft SQL Server\90\DTS\Tasks. Restart SQL Server
Business Intelligence Development Studio, and you should see your custom
connection manager in the list of available connection managers.
Listing 1. Custom task example
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
namespace CustomTask
{
[ DtsTask(
DisplayName = "Custom Task",
Description = "Description of custom task",
IconResource = "CustomTask.TaskIcon.ico" ) ]
public class CustomTask : Task
{
private int _sleepPeriod = 3000;
// TODO: Implement task properties as needed. These properties will appear
// in Properties window when you select your task in the Control Flow.
public int SleepPeriod
{
get { return _sleepPeriod; }
set { _sleepPeriod = value; }
}
public override DTSExecResult Validate(Connections connections, VariableDispenser
variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log)
{
// TODO: Add your validation code here.
return DTSExecResult.Success;
}
public override DTSExecResult Execute(Connections connections, VariableDispenser
variableDispenser, IDTSComponentEvents componentEvents,
IDTSLogging log, object transaction)
{
// TODO: Add your execution code here. The following is an example.
bool fireAgain = false;
componentEvents.FireInformation(1, "", "Sleeping for " + _sleepPeriod.ToString() + " ms"
, "", 0, ref fireAgain);
System.Threading.Thread.Sleep(_sleepPeriod);
componentEvents.FireInformation(2, "", "Woke up", "", 0, ref fireAgain);
return DTSExecResult.Success;
}
}
}
|
