Extending Integration Services with the Script Task and Script Task Plus
By Ivan Peev, Managing Partner at CozyRoc LLC. CozyRoc offers a commercial library of custom tasks, components, and scripts for use with SQL Server Integration Services.
Introduction
SQL Server Integration Services is a well-designed framework for developing ETL solutions. It is a radically different replacement for the very popular Data Transformation Services (DTS) product in SQL Server 2000. The DTS toolkit included an ActiveX Script task for implementing scripts based on VBScript and ActiveX technology. The Integration Services framework provides an equivalent task, but this time based on the vastly more powerful and secure .NET Framework. This article demonstrates the Integration Services Script task, as well as Script Task Plus, a commercial product from CozyRoc.
Why use the Script task?
The Integration Services framework comes with more than 30 specialized components, which greatly simplify the implementation of ETL solutions. In case the required functionality doesn’t exist, there are two choices available: implement a custom component, or implement a script. Implementing a script should be easy, and that is exactly what the Script task is all about.
An example of scripting using the built-in Script task
I will demonstrate the functionality of the Integration Services Script task by retrieving a list of remote files from an FTP server. This functionality is currently missing in the standard FTP task, even though the FTP connection manager supports it. The Script task will require the following parameters:
· The name of the FTP connection manager to use.
· The remote path from which to get the list of files.
· A package variable to store the list of remote files.
Step 1: Creating an FTP connection manager
Open a new or existing Integration Services package in Business Intelligence Development Studio (BIDS). To implement and test this example of using the Script task, you have to be able to connect to a running FTP server. You may be able to use the FTP server that is an optional component of Internet Information Services (IIS) for this purpose.
To create an FTP connection manager:
1. On the Control Flow tab of the designer, right-click in the bottom pane labeled Connection Managers.
2. Select New Connection… from the context menu.
3. In the Add SSIS Connection Manager dialog box, select FTP.
4. Double-click to open the FTP Connection Manager Editor dialog box.
5. Specify your FTP server and credentials.
6. Click Test Connection button to test your connection with the FTP server.
After you click OK, the connection manager will be created with an automatically generated name. You can rename it to a shorter name like FTP.
Step 2: Inserting the Script task
To add a Script task to your package:
1. Drag and drop the Script task from the Toolbox to the design surface.
2. Name the task appropriately, for example, Get FTP file list.
3. Double-click to open the Script Task Editor.
4. In the Script Task Editor, select the Script tab and click the Design Script… button to open the scripting environment.
Figure 1: The built-in Script task in the Integration Services Toolbox.
Step 3: Entering the initial script
When you open a new script, the editor contains some boilerplate code that serves as a good starting point. There is a ScriptMain class which serves as the entry point, and a Main method that runs when the package runs the Script task. For this example, all functionality is implemented in the Main method:
Public Sub Main()
Dim result As Integer
Dim manager As ConnectionManager
Dim ftpClient As FtpClientConnection
Dim foldersList As String()
Dim filesList As String()
manager = Dts.Connections("FTP")
ftpClient = New FtpClientConnection( _
manager.AcquireConnection(Nothing))
Try
If ftpClient.Connect() Then
Call ftpClient.SetWorkingDirectory("/")
Call ftpClient.GetListing(foldersList, filesList)
' Store files list in package variable.
Call Dts.VariableDispenser.LockOneForWrite( _
"ResultVar", _
vars)
Try
vars("ResultVar").Value = filesList
Finally
Call vars.Unlock()
End Try
End If
Catch ex As Exception
result = Dts.Results.Failure
Call Dts.Events.FireError( _
0, _
String.Empty, _
ex.Message, _
String.Empty, _
0)
Finally
Call ftpClient.Close()
End Try
Dts.TaskResult = result
End Sub ' Main
What this script does is very simple:
1. Get the FTP connection manager that we created earlier from the collection of connections that are defined in the package.
2. Connect to the FTP server by using the settings that we specified when we configured the connection manager.
3. Select the remote folder.
4. Retrieve the list of remote files.
5. Store the list in package variable.
Even though this script accomplishes the task at hand, it contains hard-coded values for the name of the FTP connection manager, the working directory, and the name of the package variable that stores the results. If the script is left the way it is, the user has to review and change the script code each time it's used, according to his or her needs. Furthermore, the script is not reusable. You can copy and paste it where you need it, but this solution is not easy to maintain.
An example of scripting using Script Task Plus from CozyRoc
Is there a way to improve the script? Wouldn’t it be nice to be able to configure your script in a dialog box? Wouldn’t it be great to maintain the script in one central place, and reuse it in as many packages as you want?
Introducing Script Task Plus
Script Task Plus (http://www.cozyroc.com/ssis/script-task) is an extension of the standard Script task offered by CozyRoc LLC. It helps you set up scripts in an easy, user-friendly way. But wait, there's more! Scripts implemented with Script Task Plus can easily be reused in many packages, and modified and maintained in just one place. To demonstrate and test Script Task Plus, you have to download and install the SSIS+ library from http://www.cozyroc.com/products.
Step 1: Adding Script Task Plus to the Toolbox
After you install SSIS+, you have to include Script Task Plus in your Integration Services toolbox:
1. With an Integration Services project open in BIDS, right-click on the Toolbox.
2. Select Choose Items… from the context menu.
3. In the Choose Toolbox Items dialog box, select the Integration Services Control Flow Items tab.
4. Scroll-down and find Script Task Plus.
5. Click the check box next to it.
Figure 2: Including Script Task Plus in the Integration Services toolbox.
When you click OK and close the dialog box, Script Task Plus will appear in your Toolbox. Insert it in your package and double-click it to see the CozyRoc Script Task Editor dialog box. The dialog box looks very similar to the standard Script task dialog box. There is one additional tab called Initialize. When selected, the Initialize tab shows a property grid that contains the script setup parameters. The grid will be empty initially, because the script parameters have yet to be implemented. Select the Script tab and click the Design Script… button to open a new script project and begin creating your script.
Step 2: Referencing the SSIS+ library in your script
Before we implement the script parameters, the SSIS+ library has to be referenced in the script project.
1. On the Project menu, select Add Reference.
2. In the list of available .NET components, select CozyRoc.SSISPlus library and click Add button.
3. Click OK to close the dialog.
4. In your code, include an Imports statement like the following:
Imports CozyRoc.SqlServer.SSIS.Attributes
Figure 3: Referencing the SSIS+ library in your script project.
Step 3: Implementing script parameters in the revised script
The Script Task Plus parameters are implemented in the entry point class as public member properties. You can attribute the properties with standard attributes or with attributes provided by CozyRoc. The properties will be presented in a standard Windows Forms property grid, so you can apply the attributes mentioned in the property grid documentation (https://msdn.microsoft.com/en-us/library/system.windows.forms.propertygrid.aspx).
As stated above, our revised example contains three script parameters (FtpConnection, RemotePath, and ResultVariable). Here is the improved script:
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Class ScriptMain
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Sub Main()
Dim result As Integer
Dim manager As ConnectionManager
Dim ftpClient As FtpClientConnection
Dim foldersList As String()
Dim filesList As String()
Dim vars As Variables
' Get FTP connection client.
manager = Dts.Connections(Me.FtpConnection)
ftpClient = New FtpClientConnection( _
manager.AcquireConnection(Nothing))
Try
If ftpClient.Connect() Then
' Set current working directory.
Call ftpClient.SetWorkingDirectory(Me.RemotePath)
' Get remote files list.
Call ftpClient.GetListing(foldersList, filesList)
' Store files list in package variable.
Call Dts.VariableDispenser.LockOneForWrite(Me.ResultVariable, vars)
Try
vars(Me.ResultVariable).Value = filesList
Finally
Call vars.Unlock()
End Try
End If
Catch ex As Exception
result = Dts.Results.Failure
Call Dts.Events.FireError( _
0, _
String.Empty, _
ex.Message, _
String.Empty, _
0)
Finally
Call ftpClient.Close()
End Try
Dts.TaskResult = result
End Sub ' Main
#Region "Properties"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
<Connection("FtpConnectionType")> _
<Description("Specifies FTP connection to remote server.")> _
Public Property FtpConnection() As String
Get
FtpConnection = m_ftpConnection
End Get
Set(ByVal value As String)
m_ftpConnection = value
End Set
End Property ' FtpConnection
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
<Description("Specifies remote path to get files listing.")> _
Public Property RemotePath() As String
Get
RemotePath = m_remotePath
End Get
Set(ByVal value As String)
m_remotePath = value
End Set
End Property ' RemotePath
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
<Variable()> _
<Description("Specifies package variable where you would like to store the result.")> _
Public Property ResultVariable() As String
Get
ResultVariable = m_resultVariable
End Get
Set(ByVal value As String)
m_resultVariable = value
End Set
End Property ' ResultVariable
#End Region ' Properties
#Region "Internals"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private ReadOnly Property FtpConnectionType() As String()
Get
FtpConnectionType = New String() {"FTP"}
End Get
End Property ' FtpConnectionType
#End Region ' Internals
#Region "Attributes"
Private m_ftpConnection As String
Private m_remotePath As String
Private m_resultVariable As String
#End Region ' Attributes
End Class ' ScriptMain
After you compile the script and close the script editor, select the Initialize tab once again. What a difference! The member properties that you defined in your code are now exposed in a property grid. Now setting up the parameters does not require reviewing and modifying the script code. The setup is clean and easy.
Figure 4:Script Task Plus Editor dialog box showing how public properties become script parameters that are easy to modify.
Reusing the script
If you look at the bottom-left corner of the Script Task Plus Editor (Figure 4), there are 2 buttons. The left button is for exporting a script to a separate file. Use this command when you implement a new script and you want to make it available for reuse. The right button is for linking to a script. Use this command when you want to reuse an existing script in your Integration Services package. If you want to break the link to a script, press the right button again.
Obtaining the script used in this example
You can download the improved script discussed in this article from the free public script repository at: http://www.cozyroc.com/script/get-ftp-file-list-task.
Conclusion
SQL Server Integration Services provides a well-documented way to develop and customize your ETL solutions. The built-in Script task is based on the powerful .NET Framework and lets you to fill in any gaps in the functionality required by your projects. CozyRoc Script Task Plus is a practical extension of the Script task that allows easier script setup and better reuse. It provides flexibility, while keeping things simple.
About the author**. Ivan Peev is Managing Partner with* CozyRoc *LLC, a software development and services company based in Raleigh, NC. Ivan has 17 years of experience building software products and solutions. For the last 8 years, he has been building business intelligence and data warehousing products used by Fortune 100 companies. Ivan has deep practical knowledge of the Microsoft business intelligence technology stack, and was well-versed in the object-oriented programming methodology long before it became mainstream.