programming4us
programming4us
DATABASE

Using SQL Server 2005 Integration Services : Extensibility (part 1) - Script Tasks

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
7/7/2012 6:16:18 PM
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.

Figure 1. 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;
}
}
}			  

Other  
  •  .NET Compact Framework 3.5 : Working with Data Sets (part 3) - Reading and Writing a Data Set as XML
  •  .NET Compact Framework 3.5 : Working with Data Sets (part 2) - Data Binding
  •  .NET Compact Framework 3.5 : Working with Data Sets (part 1) - Creating and Accessing DataSet, DataTable, and DataView Objects
  •  .NET Compact Framework 3.5 : Examining ADO.NET
  •  Using SQL Server 2005 Integration Services : Programming Integration Services (part 4) - Connecting the Source and Destination Adapters with a Path
  •  Using SQL Server 2005 Integration Services : Programming Integration Services (part 3) - Setting Up Column Information
  •  Using SQL Server 2005 Integration Services : Programming Integration Services (part 2)
  •  Using SQL Server 2005 Integration Services : Programming Integration Services (part 1) - Creating Packages Programmatically - Data Flow
  •  Using SQL Server 2005 Integration Services : Working with Integration Services Packages (part 2) - Data Flow
  •  Using SQL Server 2005 Integration Services : Working with Integration Services Packages (part 1) - Control Flow
  •  
    Top 10
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
    - Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
    - Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
    REVIEW
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    programming4us programming4us
    programming4us
     
     
    programming4us