Script Task Samples

The sample solution for this chapter has several packages that illustrate how to build Script Tasks and use them in packages. Each package shows a way to accomplish some useful task. Open the packages and try to figure out how they work. Change something and see how it affects the package. The packages are simple so they'll be easy to understand and primarily focus on one class of problem. You can use these packages as a starting point for your own packages when you're trying to solve a similar problem. The following sections briefly describe each of the sample packages and how they work.

Working Against the SSIS Object Model

The two packages ParentPackage and ChildPackage show how to modify an existing package, save it, and then execute it. The problem these packages solve is how to dynamically change the Transfer Objects Task to move a table. Because the Transfer Objects Task uses a StringCollection to specify the tables to move, property expressions don't work here. So, instead, you can build a StringCollection with the values you want to set, open the package containing it, modify the TablesList property to contain the new StringCollection you just built, and save the package. The StringCollection class is defined in System.Collections.Specialized, so you can also add an Imports statement for it, as follows:

Imports System.Collections.Specialized Public Class ScriptMain      Public Sub Main()          Dts.TaskResult = Dts.Results.Success          Try               ' Create an Application object to load the package               Dim application As Microsoft.SqlServer.Dts.Runtime.Application               Application = New Application()               ' Use the File Connection Manager to get the package name               Dim packagename As Object               packagename = Dts.Connections("MoveTablesPackage").AcquireConnection(Nothing)               ' Load the package               Dim package As Microsoft.SqlServer.Dts.Runtime.Package               application.LoadPackage(packagename.ToString(), Nothing)               ' Get the task host that contains the Transfer Objects Task               Dim th As TaskHost               th = package.Executables("TablesToMove")               ' Build a String Collection with value of the table to move               ' You can add as many table names as you want here.               Dim sc As StringCollection = New StringCollection()               sc.Add(Dts.Variables("TABLETOMOVE").Value.ToString())               ' Use the properties collection to set the               ' TableList property to point to our new StringCollection               th.Properties("TablesList").SetValue(th, sc)               application.SaveToXml(packagename, package, Nothing)           ' Catch any exceptions and log them.           Catch ex As Exception               Dts.Events.FireError(51, ex.TargetSite().ToString(), ex.Message, "", 0)               Dts.TaskResult = Dts.Results.Failure           End Try      End Sub End Class 

The ChildPackage package has only one task in it called TablesToMove. If you run the parent package, it executes the child package and moves the tables you set. You'll need to modify the child package to point to the correct servers. Also, if you run this sample in the designer, it detects that the child package file has changed and asks you if you want to reload. Select Yes so the designer will run the newly saved package. If you run the sample under DTExec, this isn't a problem.

Downloading a File Using HTTP

This sample, DownloadFileViaHTTP.dtsx, shows how to get a connection manager from the Connections collection and call AcquireConnection on it. This also shows how to work with the somewhat peculiar HTTP Connection Manager. The HTTP Connection Manager returns an object that essentially implements all functionality for retrieving files.

This code calls AcquireConnection on the HTTP Connection Manager to get the HttpClientConnection and download the file named in the DOWNLOADEDFILE variable.

Public Class ScriptMain    Public Sub Main()      '      ' Create an HttpClientConnection and use it to download      ' a file from the location the connection manager specifies      '      Dim httpConnection As Microsoft.SqlServer.Dts.Runtime.HttpClientConnection      Dim temp As Object      ' Try to get the connection      Try        temp = Dts.Connections("FileToDownload").AcquireConnection(Nothing)        httpConnection = New HttpClientConnection(temp)        httpConnection.DownloadFile(Dts.Variables("DOWNLOADEDFILE").Value.ToString(), True)      Catch ex As Exception        Dts.Events.FireError(1, ex.TargetSite.ToString(), ex.Message, "", 0)      End Try      ' Return that we succeeded      Dts.TaskResult = Dts.Results.Success      End Sub End Class 

A few things are going on in this package that might not be obvious. The Execute Process Task has two property expressions that specify the correct working directory and executable to run. The connection manager is just pointing to the Microsoft.com website, but doesn't specify a file, so the connection manager picks up the default home page. And finally, the DOWNLOADEDFILE variable has an expression that builds the fully qualified filename from the WORKINGFOLDER variable and the name Temp.html.

@WORKINGFOLDER + "\\" + @FILENAME 

Finally, the Execute Process Task launches the default browser showing the Microsoft.com home page.

This package uses the Script Task and the expressions to pull together stock functionality in the package to build a fairly complex application with very little code. Hopefully, you can see that you can take this package as a start and use it anytime you need to access files on a website.

Loading the Contents of a Text File into a Variable

LoadingATextFile.dtsx is a simple package that uses a Foreach Loop with a Foreach File Enumerator to loop over text files in a folder. The Script Task in the Foreach Loop opens each text file and displays the contents in a message box. The sample also shows how to split up the code to use a subfunction. Make sure there are some .txt files in the working folder before running this package.

The following code reads the name of a file to open from the FileName variable and loads the contents of the file into a string, which it then displays in a message box and also places the contents in the variable named FileContents.

Imports System.IO Public Class ScriptMain     '     ' Load a file and show a message box with the contents.     '     Public Sub Main()          Dim errorInfo As String = ""          Dim Contents As String = ""          Contents = GetFileContents(Dts.Variables("FileName").Value.ToString(), errorInfo)          If errorInfo.Length > 0 Then              ' Failed to load file              MsgBox(errorInfo, MsgBoxStyle.Critical, "Error")              Dts.TaskResult = Dts.Results.Failure          Else              ' Do real work here.              MsgBox(Contents, MsgBoxStyle.OkOnly, "File contents")              Dts.Variables("FileContents").Value = Contents              Dts.TaskResult = Dts.Results.Success          End If     End Sub     '     ' Function for getting the contents of a text file     '     ' A subfunction for loading the file.     Public Function GetFileContents(ByVal filePath As String, _                                          Optional ByVal ErrorInfo As String = "") As String          Dim strContents As String          Dim objReader As StreamReader          Try              objReader = New StreamReader(filePath)              strContents = objReader.ReadToEnd()              objReader.Close()              Return strContents          Catch Ex As Exception              ErrorInfo = Ex.Message          End Try     End Function End Class 

Simple Custom Logging

The CustomLogging.dtsx package is very simple and shows how to use the Log method to create a log entry.

Public Sub Main()         '         ' Add an entry into the logs.         '         Dts.TaskResult = Dts.Results.Success         Try             Dts.Log("This is a custom log entry from the Script Task", 42, Nothing)             Dts.Variables.Add("DummyVarName", False, "", Nothing)         Catch ex As Exception             Dts.Log(ex.Message, 0, Nothing)             Dts.TaskResult = Dts.Results.Failure         End Try    End Sub 

Handling Exceptions

The HandleException package attempts to do something illegal that causes an exception and handles the exception by firing an error and returning an execution result of failed, exiting gracefully. Notice also that the main class name is EntryPoint, not Main. Nothing too important, just noting that it doesn't matter so long as the EntryPoint property on the Script Task dialog box has the same name.

Public Class EntryPoint      Public Sub Main()           ' This is a simple script that attempts to do something illegal,           ' create a new variable at execution time.           ' Ensure that the error is handled and logged.           ' Initialize the task result to success.           Dts.TaskResult = Dts.Results.Success           Dts.Events.FireProgress("HandleException", 0, 0, 0, "TestExpressions", True)           Try               ' Try to add a new variable               Dts.Variables.Add("TestVariable", False, "", "Test value")           Catch ex As Exception               ' Write out an error, with the exception generating target site               ' and the exception message               Dts.Events.FireError(1, ex.TargetSite.ToString(), ex.Message, "", 0)               ' Always execute this code and return gracefully.               ' Never return with an exception from script tasks.               Dts.TaskResult = Dts.Results.Failure           End Try           Dts.Events.FireProgress("HandleException", 100, 0, 0, "TestExpressions", True)      End Sub End Class 

Safe Message Box

The SafeMessageBox package uses a small function to determine if it is OK to show the message box. Using this function, you can avoid the embarrassing and possibly costly mistake of showing a modal message box in a package on a production machine. Notice the VariableDispenser is used in this code. This makes it so you don't have to remember to put the name of the InteractiveMode variable in the Script Task dialog box.

' A function for safely showing message boxes in script tasks Public Function SafeMessageBox(ByVal prompt As String, ByVal title As String)      ' Default is off.      Dim showMessage As Boolean = False      Try           ' Get the System InteractiveMode variable to determine if we should show           Dim vars As Variables           Dts.VariableDispenser.LockForRead("System::InteractiveMode")           Dts.VariableDispenser.GetVariables(vars)           showMessage = vars("System::InteractiveMode").Value      Catch ex As Exception           ' Fire an error, we weren't able to get the variable           Dts.Events.FireError(1, ex.TargetSite.ToString(), ex.Message, "", 0)      Finally           If showMessage Then               ' InteractiveMode is True               MsgBox(prompt, MsgBoxStyle.OkOnly, title)           End If      End Try End Function