Introduction

On November 7, 2005 Microsoft released SQL Server 2005 together with Visual Studio 2005 and the .NET Framework 2.0. One big enhancement in the new version of SQL Server was in the Data Transformation Services (DTS) which has been replaced by the SQL Server Integration Services (SSIS). SSIS provides a visual designer hosted in the Business Intelligence Development Studio of SQL Server 2005 where you can design the flow of your SSIS packages. Figure 1 shows an example of this new designer.

NOT VALID: ImageTooWide: The new SSIS designer in SQL Server 2005
This figure has been reduced in size to fit in the text. To view the full image Click here

SSIS comes with a huge list of tasks which can be used out of the box in SQL Server 2005. But this is not the end of the story; if you are an experienced developer you can write countless extensions for SSIS to customize its behavior. For example, you can write your own custom:

• control flow tasks


• data flow tasks


• transformations


• input sources


• destination sources

And you can also use .NET code inside a script component

A Scenario: Importing Data

Before digging into a concrete solution, I want to define the scenario and the problem that the sample application is trying to resolve using the SSIS solution described in this article. Let’s assume that you have to implement a data import where you must validate some data with business logic you have written, for example inside a custom .NET assembly. There are many ways to accomplish this:

• Write the complete import logic in a .NET application, use a custom .NET assembly with the business logic and schedule the executable via the Windows task scheduler


• Implement the import logic with SSIS and do the validation in a stored procedure (T-SQL or managed code)


• Use a script task inside SSIS and reuse the custom .NET assembly with your own business logic.

I’ve chosen the third solution because it’s the best one regarding the maintainability and code reuse of the application. Listed below are some reasons for this:

• Complex validation rules and business logic implemented directly in T-SQL code isn’t a good approach and design


• It’s very hard to reuse T-SQL code in other projects (ASP.NET, WinForms, Mobile Devices)


• You can unit test your custom .NET assembly directly from Visual Studio 2005

The sample solution implements the data import of orders stored in a comma separated values (CSV) file. The file is named DataImport.txt and is stored in the folder CodeSSIS_Sample in the sample download. Each line consists of information such as the CustomerID, ProductID, Quantity and some information about the payment (credit card number, expiration date and card holder). Let’s assume that we now have the requirement to validate the credit card number during the CSV data import. To do this I have written a custom assembly in C# which I’m reusing in the SSIS package. The result of this design is that you have also the possibility to reuse the newly created credit card validation algorithm coded in C# directly in other applications .

System Requirements

To run the code for this sample you will need the following:

• SQL Server 2005 running on Windows 2000 or later


• Visual Studio 2005 Standard Edition or higher

Installing and Compiling the Sample Code

The sample download for this article contains a Visual Studio 2005 solution (containing 2 projects). The solution implements a credit card number validation assembly and the SSIS package used for implementing the file import.

To install the sample you must first compile the CreditCardValidation project and copy the output assembly CreditCardValidation.dll to the following folder: %systemroot%Microsoft.NETFrameworkv2.0.50727

Furthermore you must also install the assembly into the Global Assembly Cache (GAC). Use the following command from the command line (make sure you use the Visual Studio 2005 command prompt):

gacutil.exe /i CreditCardValidation.dll

The only thing you must change in the SSIS package is the connection string of the DataImport connection. This connection string must point to the correct path of the CSV file you want to import. The solution already provides a default import file called DataImport.txt which you can find in the folder SSIS_Sample.

When all these steps are done successfully you are ready to run the SSIS package called ManagedCode. You can do this directly from the SQL Server Business Intelligence Development Studio or directly with a double-click on the SSIS package in Windows Explorer.

SSIS Scripting

This section will provide a short introduction to the scripting capabilities of SSIS. This is essential if you want to write advanced custom scripts. Including a custom script in a SSIS package is very easy because SSIS offers you a separate task dedicated to do so. Figure 2 shows you where you can find this task in the toolbox.

Figure 2. The scripting task in SSIS

As you can see from Figure 2 the scripting task can be used inside the Control Flow of your SSIS package. As soon as you put the Scripting Task on the designer surface you can start to customize the task within the designer environment. With a double click you can open the Script Task Editor as shown in the Figure 3.

Figure 3. The Script Task Editor window

The interesting thing in this window is the Script tab item. Within this tab item you can configure the whole scripting aspects of this task. SSIS provides the property ScriptLanguage where you can define the programming language used to implement the custom script. Unfortunately SSIS currently offers only VB.NET as a programming language – no C# anywhere! Hopefully other languages will be supported in future releases of SQL Server.

Another very important property is PrecompileScriptIntoBinaryCode. With this flag you can control whether the custom script should be compiled into an assembly (to MSIL code). You should always set this flag to true (which is the default value), because it will boost the performance of your SSIS package. If you want to access SSIS variables within your scripting task you must add these variables through the properties ReadOnlyVariables and ReadWriteVariables. If you have more than one variable you can separate them with a comma. After setting up the common properties of the script task you can click the Design Script button to implement your script accordingly. Clicking this button will launch the Microsoft Visual Studio for Applications where you can write your custom script as seen in Figure 4.

NOT VALID: ImageTooWide: The Microsoft Visual Studio for Applications IDE
This figure has been reduced in size to fit in the text. To view the full image Click here

The following listing shows the code skeleton of the VB.NET code which is automatically inserted into the script.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain
   Public Sub Main()
      Dts.TaskResult = Dts.Results.Success
   End Sub

End Class

As you can see from the listing above the string Dts appears throughout the script. This is one hint where you can see that SSIS is the next version of DTS (Data Transformation Services). Unfortunately Microsoft hasn’t changed the names of the members in the final release of SQL Server 2005. The class Dts provides very easy access to SSIS packages. The following list shows the most important members and their uses:

• Dts.Connections – provides a collection of all connection objects defined in the SSIS package


• Dts.Events – gives the ability to fire events directly from a scripting task


• Dts.Transaction – provides direct access to the underlying transaction used to execute the SSIS package


• Dts.Variables – provides a collection with all variables available to the scripting task

Below are a couple of simple tips that I found helpful when developing my SSIS scripts -if you defined a variable called MyVariable you can output the content in a message box with the following script:

Public Class ScriptMain
   Public Sub Main()
      MessageBox.Show(Dts.Variables("MyVariable").Value.ToString());
      Dts.TaskResult = Dts.Results.Success
   End Sub

End Class

It is very important to know that you can access the real value of a variable with the property Value. If you are calling the method ToString() directly on a variable object you will get back the fully-qualified-name of the underlying .NET class! With this know-how you can now have a closer look at how a custom .NET assembly can be called from within a scripting task in a SSIS package.

Managed Code in Script Components

As you have seen from the previous listing it is very easy to call functionality within SSIS which is implemented in another assembly of the .NET Framework. But wouldn’t it be better if you could use functionality in a .NET assembly you implement? This would provide you with the ability to reuse business logic and provide the ultimate in flexibility for implementing business rules. Using a .NET assembly is possible but can be a bit tricky to do this as you will see at the following pages.

Part of the problem I am trying to solve is that I want to import with a SSIS package orders which are stored in a CSV file on the file system. The following listing shows a few sample records from this file.

AKS,CMS,4,4567-4345-6559-5345,09/2008,Klaus Aschenbrenner

MSFT,VS,10,8909-3534-2422-9003,10/2010,Bill Gates

As you can see from the listing, a comma is used as a separator between each column. The requirements of this file import are that each record is processed and that the credit card number is validated by a custom component written in C#. With this information in hand I can start to develop a SSIS package for the data import.

In the first step I’ve created a new package inside the SSIS designer. To implement the data import, I’ve placed a Data Flow Task inside the Control Flow of the newly created package. Next I double-click on the Data Flow Task which takes me directly to the Data Flow Designer. To import data from a flat file (like a csv file) I’ve taken the Flat File Source component and placed it on the designer surface. With a double-click at the new component I can configure a Connection Manager which points to the csv file I want to import. Within the Flat File Connection Manager Editor I’ve configured each import column from the csv file as described in the following table:

Column name	Data type
CustomerID	string
ProductID	string
Quantity	integer
CreditCardNumber	string
ExpirationDate	string
CreditCardHolder	string

After you configure the import columns you have the possibility to preview the import data within the Flat File Connection Manager Editor as shown in Figure 5.

Figure 5. Import preview of a csv file

As soon as I have configured the Flat File Source, I can place a Script Component on the designer surface and connect it with the previously created Flat File Source. When I place the Script Component on the designer surface a dialog pops up where I will be asked which type of Script Component I want to create, as seen in Figure 6.

Figure 6. Defining the type of Script Component

Here you have the following options:

• Source – here you can implement a custom Source Component, such as when you need to read a custom binary file format


• Destination – here you can implement a custom Destination Component, such as when you need to write to a custom binary file format


• Transformation – here you can implement transformation logic during the processing of your input data

Because I want to call custom business logic for the credit card validation during the import of the data, I’ve chosen the option Transformation from this dialog. When I double-click on the Script Component the Script Transformation Editor will be brought up. This is where I can configure the input and the output columns as well as have the ability to write custom script in VB.NET. Within the tab Input Columns I’ve selected all the available columns so that each input columns is also provided as an output column from the Script Component.

Now I will concentrate on the output columns. It would be very nice if you could define an additional column which stores a flag if the credit card number was validated successfully. Happily, you can configure the Script Component in a way so that an additional output column can be created. To this I just select the Inputs and Output tab and in the treeview in the middle of the dialog I select the node Output 0/Output Columns as shown in Figure 7.

Figure 7. Defining a new output column

Here I can now click on the button Add Column to add a new column to the output of the Script Component. Next I rename the column to CreditCardNumberValid and choose the datatype Boolean. Now I am have everything configured and am ready to start to write the VB.NET scripting code. To do this I select the Script tab and click the button Design Script. This action launches the Microsoft Visual Studio for Applications as you have already seen previously. As you can see below the script differs slightly from the script created by a Script Task in the Control Flow.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
   Inherits UserComponent
   Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
   End Sub

End Class

The first big difference is that the ScriptMain class inherits from a class called UserComponent. When you take a look in the Project Explorer on the left of the window you can see an additional file called ComponentWrapper. When you open this file you can see the class definition for the class UserComponent. Furthermore there is also a file called BufferWrapper which defines the class Input0Buffer used as parameter for the method Input0_ProcessInputRow(). Input0_ProcessInputRow() is called for each record processed in the Data Flow Task. Here you have the ability to do some additional checks with the imported data such as calling the credit card validation routine. The class Input0Buffer also offers typed properties for each input and output column defined previously.

Now let’s take a look at how I called the custom component implemented in another assembly. In the first step I’ve created inside Visual Studio 2005 a new C# class library and added the class CreditCardValidator. The following listing shows the class definition.

using System;

using System.Collections.Generic;

using System.Text;

using System.Windows.Forms;

namespace CreditCardValidation

{
   public class CreditCardValidator
   {
      public bool Validate(string CreditCardNumber)
      {
         MessageBox.Show("CreditCardNumer: " + CreditCardNumber,
           "CreditCardNumber Validation from Managed Code");
         return true;
      }
   }

}

As you can see from the listing the class CreditCardValidator offers the method Validate() which returns a boolean indicating if the validation of the credit card number was successful. For demonstration purposes I’ve added a call to MessageBox.Show() to verify that the custom component was called and return true for a successful validation. In a real project you would implement your own business logic here.

The interesting question is now how can I use this component inside the SSIS script. Unfortunately I can’t reference a custom component inside SSIS. When you go back to the Script Editor you see on the left side of the window all of the referenced assemblies. If you right-click on the References you have the ability to add a reference to another assembly. But as soon as the dialog has opened you can see that there is no option to reference a custom assembly. This is a very disappointing omission that will hopefully be fixed in a future release.

The only solution to this problem at this time is to copy the custom assembly into the GAC or in the folder %systemroot%Microsoft.NETFrameworkv2.0.50727. Now when you reopen the Add Reference dialog you can see that your custom assembly is also available. Now that I have a reference to the custom assembly, I can implement the method Input0_ProcessInputRow() as follows.

Dim validator As New CreditCardValidator

Public Overrides Sub Input0_ProcessInputRow(ByVal Row as Input0Buffer)
   Row.CreditCardNumberValid = validator.Validate(Row.CreditCardNumber)

End Sub

Now it seems that everything should be in place to work. When I try to start the package I get an error saying that the custom assembly isn’t found. So why is this? It took me several hours to find out that I must also place the custom component in the GAC (global assembly cache) so that it can be found from the SSIS package. You can add your assembly with the following command from the command line to the GAC:

gacutil.exe /i CreditCardValidation.dll

Now if I try to run the SSIS package everything works and for each row in the csv file a message box is displayed with the current credit card number. So let’s now repeat the steps which are necessary to call a custom component from a SSIS script:

• Place the assembly in the folder %systemroot%Microsoft.NETFrameworkv2.0.50727 so that you can reference the assembly inside the Microsoft Visual Studio for Applications


• Place your assembly in the GAC so that it can be found during the execution of the SSIS package


• When you must update your assembly, only update the GAC with the option /f, not the folder %systemroot%Microsoft.NETFrameworkv2.0.50727, because this folder is only used so that Microsoft Visual Studio for Applications can find your assembly for referencing purposes

Some thoughts on SSIS scripting

As you have from this article, it can be tricky to call a custom component from inside a SSIS script. It would have been very nice if Microsoft had put more thought into this point because I think that calling a custom component inside SSIS is a task that will only increase over time.

When you also have a detailed look at the scripting capabilities of SSIS, you must also consider if you do the scripting inside Microsoft Visual Studio for Applications or if you implement your custom "script" in a .NET assembly and reference the assembly inside the SSIS script. There are several advantages when you use a custom .NET assembly:

• When you place your script code in a custom assembly the code can be reused. When the script is implemented directly in SSIS the code can’t be used in other projects!


• The script editor of SSIS offers you no possibilities for unit testing. So it’s not a very good approach to place complicated business logic rules directly into SSIS scripts, because you can’t automatically test them!


• You can only place a whole SSIS package under version control like Team Foundation Server. So only one team member can work on a SSIS package and the underlying script. But when you place your script in a custom .NET assembly several team members can work in parallel on the assembly.

But there is also one drawback when you use custom assemblies in a SSIS package, there is no xcopy deployment possible, because an administrator must place the assembly into the GAC – just keep this in mind!

Conclusion

This article has shown how you can call a custom .NET assembly from a SSIS script. As you can see from this article it’s very tricky to do this, because you must copy the assembly to several folders and register it in the GAC. Hopefully there is a better support for this scenario in a future version of SQL Server. As you have seen there are several advantages when you implement your SSIS script in custom assemblies. The only drawback to this solution is that the assembly must be placed in the GAC, and therefore you need an administrator for deploying your SSIS package. I hope that I have inspired you with this article and that you also see the power of the new SQL Server 2005 features.