Creating Custom Tasks with SSIS

Recently i started working with SSIS. I had to develop a workflow that will allow me to copy different files/directories from various locations into a local folder. I used the FileCopy Task Provided in SSIS. But to copy from multiple sources to multiple/single target, i need to use multiple FileCopy tasks. So, i thought, i should probably write a simple code to do it and SSIS supports using couple of interfaces. All you need to know is about those interfaces and implement them.

I am going to use SQL Server 2008 and VS 2008 for this purpose. You need to have both software in the machine.

Source Code Overview – The source code holds two projects, one project with a single class that holds the actual functionality. Another project holds classes/forms to display the UI for the source code class. I am separating the UI code with the actual functionality for ease.

Source Code Project
• Open VS 2008, click New Project from and select "Class Library Project" under visual C# project type
• Type the Project Name as "CustomFileCopyTask"
• Uncheck the "Create Directory for Solution" option and save the project into the folder, SSISCustomTasks
• Rename the class1.cs under the project to "clsCustomFileCopyTask"
• Add a reference to an assembly called, Microsoft.SqlServer.ManagedDTS. This assembly is registered under GAC (C:\Windows\assembly\GAC_MSIL folder in Vista). So, use the browse tab of the references dialog to add a reference to this dll
• This class needs to be derived from an abstract class Task and IDTSComponentPersist interface.
• Define the properties Name, Description and FileSourceColl. Because this class is going to have multiple file sources, defining the filesources as a collection property. To hold these values, we are going to define a custom class called, FileSource
public class FileSource : Component
{
private String _source;
public String Source
{
get { return _source; }
set { _source = value; }
}

private string _target;
public string Target
{
get { return _target; }
set { _target = value; }
}

private string _desc;
public string Description
{
get { return _desc; }
set { _desc = value; }
}
}
• All custom classes that need to get display in the property grid of the task should derive from the component class
• Override the Validate and Execute methods of the Task class. Validate method is a design time property of the task to add any validations around the property values. Execute method is the runtime property of the class.
• In the validate method, I am validating for the source directory existence and the validity of the paths. I am taking the help of the private function called, IsValidPath. Check the source code attached
• In the Execute method, Copying the source directory to the target location for each source/destination pair defined in the FileSourceColl property. Again, I am taking help of a private recursive function to copy the contents
private bool CopyDirectory(string SourcePath, string DestinationPath, bool overwriteexisting)
{
bool ret = false;
try
{
SourcePath = SourcePath.EndsWith(@"\") ? SourcePath : SourcePath + @"\";
DestinationPath = DestinationPath.EndsWith(@"\") ? DestinationPath : DestinationPath + @"\";

if (Directory.Exists(SourcePath))
{
if (Directory.Exists(DestinationPath) == false)
{
Directory.CreateDirectory(DestinationPath);

}

foreach (string fls in Directory.GetFiles(SourcePath))
{
FileInfo flinfo = new FileInfo(fls);
flinfo.CopyTo(DestinationPath + flinfo.Name, overwriteexisting);
FileInfo flTargetFile = new FileInfo(DestinationPath + flinfo.Name);
flTargetFile.IsReadOnly = false;
}
foreach (string drs in Directory.GetDirectories(SourcePath))
{
DirectoryInfo drinfo = new DirectoryInfo(drs);
if (CopyDirectory(drs, DestinationPath + drinfo.Name, overwriteexisting) == false)
ret = false;
}
}
ret = true;
}
catch (Exception ex)
{
ret = false;
}
return ret;
}
• Implement the two methods, LoadXML and SaveXML methods of the IDTSComponentPersist interface. These two methods are useful for storing and retrieving the property values of this task into the package. We don’t need to implement this interface unless we are using any complex datatypes in the custom task. But we are using the FileSource class to hold the multiple values and that is the reason we are going to implement these methods
• Add a DTSTask attribute to the “clsCustomFileCopyTask” with 3 attributes called, DisplayName, Description and UITypeName (UITypename should be Namespace.UITaskname, Assemblyname)
UI code Project
This project is going to have 3 different objects. A single class file, a windows form and a user control.
Class File – Is used to instantiate the windows form to display the property page of the task
Windows File – This is the form used that displays the property sheet of the task
User control – Is the one that displays the filesource class with the properties added upon clicking the fileSourcesColl property in the windows form

• Add a New c# class library project to the same solution with the name as “CustomFileCopyTaskUI”
• Rename the class1.cs to “clsCustomFileCopyTaskUI.cs” and add a reference to the dll, Microsoft.SqlServer.Dts.Design and “Microsoft.SqlServer.ManagedDTS” dll in the GAC
• Add a using statement for referring to the assembly
• Derive the class from IDTSTaskUI interface of the DTs.Design.Runtime library and implement the Getview() and Initialize methods of the interface. Remove the not implemented exception statements from the remaining methods of the interface
• Add the source project as a reference to the UI project

• Add a windows form named, clsCustomFileCopyTaskForm to the project, derive it from the base class called DTSBaseTaskUI. This base class is used to display the default property pages provided by SSIS
• Add a reference, Microsoft.DataTransformationServices.Controls.dll from the GAC and the related using statements

• Add a new user control item to the project and name it as clsCustomFileCopyTaskView.cs
• Add the using statement for Microsoft.DataTransformationServices.Controls
• Derive the control from IDTSTaskUIView interface apart from the usercontrol
• Implement the OnCommit, OnInitialize and OnValidate methods of the interface
Building the Sample
• As mentioned earlier, all custom tasks need to be registered in GAC before use. Follow the steps provided below for GAC registration
o Open Visual Studio command prompt and navigate to the folder where the project is saved
o Type “sn –k CustomFileCopyTaskKey.snk” to generate a private key
o Extract the public key token from the private key generated using the command “sn –k CustomFileCopyTaskKey.snk CustomFileTaskPublicKey.snk”
o Display the public key token using the command “sn –t CustomFileCopyTaskPublicKey.snk”
o Copy the token to clipboard
o In the code, locate the DtsTask attribute. In that line of code, replace the existing value of the public key token in the* UITypeName* attribute property in the IncrementTask.cs. The alphanumeric value after "PublicKeyToken=" should be replaced with the one displayed in the previous step
o Add the private key both the projects
• Open the source project properties, click on the Build Events tab and add the following commands into the post Build event command line box
copy "$(TargetDir)" "C:\Program Files\Microsoft SQL Server\100\DTS\Tasks" /y
"C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\gacutil.exe" /if "$(TargetPath)"

Above commands, first copies the dll to the Tasks folder of the SSIS, which is mandatory the custom tasks to get displayed in SSIS. Second one registers the dll’s into the GAC (Notice the /if command, reinstalls the assembly even though it already exists in the assembly)
• Click on the “Signing” tab of the project properties and check the “Sign the assembly” checkbox. From the list of strong name key files, choose the file generated in earlier steps
• Follow the same project related steps for UI project also
• Build the solution and that’s it.
• Open a new BIDS (Business Intelligence Developement Studio) project in VS 2008 and the control to the toolbox.


Friends, I have referred to couple of sites and blogs for preparing this code sample. I am giving the references below but if I miss anything, please leave your comments. Also, provide me your feedback and comments on this article. I will definitely include them to update this article. I am sure there are lot of areas I left over and will try to improve in my next article.

Download the source code from here

References
• MSND Samples on Codeplex.com
• Developing Custom Task
• Data Persistency sample
• How to Edit and Persist collections with CollectionEditor