Scripting in SSIS

By : Kasim Wirama, MCDBA, MVP SQL Server

For most business requirement scenarios, use of click, drop and select property of some tasks is sufficient. But the other scenarios, you need to provide custom solution that is not provided by the tasks (such as File System task or FTP task).

In DTS, you have only one scripting option, using ActiveX Script task, but in SSIS, you have other options, using .NET scripting provided by Script Task, besides ActiveX Script task. Let’s see what’s inside ActiveX Script task.

When you open Script section at ActiveX Script Task Editor window, there are some properties, such as Language, Script and EntryMethod. Still same with SSIS predecessor, there are VBScript and Jscript language. At Script property, you can type your script here, or alternatively, you load your activeX script file from Browse button, remember that if you load from file, all script that you type in Script property will be overridden. And you specify EntryMethod that will be first executed in script. Nothing new in this task, I recommend you move to Script task rather than ActiveX script task, for several advantages:
1. Intellisense with Visual Studio environment
2. can pass parameter into script
3. can add breakpoint to your script
4. Managed in .NET environment
5. You can precompile your script for faster execution performance.

Now, explore what Script Task component look like. In Script property, there are some properties.

ScriptLanguage property currently contains only Visual Basic .NET, but in next version of SQL Server 2008, it will have options Visual C# also.

PrecompileScriptIntoBinaryCode property is set to true, will make execution faster but file size will be bigger.

EntryPoint specify entry point method, just leave it as default.

ReadOnlyVariables contains variables that are read only purpose, and WriteOnlyVariables contains variables that can be read-write purpose.

You can try it out, first make 2 variables, named it readVar and writeVar with string type, you expected when readVar is assigned to “Hello” will output to writeVar as “Hello world”. Set value “Hello” to readVar, then define in Script Task Editor, ReadOnlyVariables to readVar and ReadWriteVariables to writeVar. Click Script button and it will open another visual studio window called VSA (Visual Studio for Application). In VSA, type dts.Variables(“writeVar”) = dts.Variables(“readVar”) & “ world” and Msgbox (dts.Variables(“writeVar”).values.ToString()) before last statement (TaskResult) and procedure Main.

Now, you run the package, it will display pop up message with text “Hello World”
Script task is very useful and offer flexibility and familiar to use when you are already used to working with Visual Studio.