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.
.jpg)
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.
.jpg)
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
.jpg)
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 (http://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.
.jpg)
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.