Friday, December 30, 2005 - Posts

Professional SQL Server 2005 Integration Services Sample Chapter

In a few weeks, my new book will ship out called Professional SQL Server 2005 Integration Services. The book was a collaborative effort that involved 9 other fantastic authors incuding 3 SQL Server MVPs. The book is in tutorial form so after you're introduced to a concept, you're walked through it in example form. Special thanks to all the SSIS team at Microsoft for tech editing the book!

You can now download a sample chapter below, which contains a large tutorial that may help you get started with SSIS. I love using this example in my speaking sessions because it shows the bulk of the features from SSIS and is a real-world scenario involving looping and archiving of files. Hope you enjoy!

-- Brian Knight

 

* - Buy the book on Amazon.

* - Download Chapter 5 - Creating an End-to-End Package

Setting Variables in the Script Task in SSIS

One of the key reasons that you use the script task is to change the value of a variable at runtime. There is a lot of real-world scenarios that you would use this for. If you're reading this you have already probably thought of a few. To set a variable in the script task, there are two main methods you can use.

Method 1

This method involves using the LockOneForWrite method in the VariableDispenser class. The advantage to this method is it allows for you to read and write to the variables at runtime without having to use the ReadOnlyVariables and ReadWriteVariables options in the Script task. The price of that though is that you have to write quite a bit more code. In the following example, I'm going to open the AlertAdmin variable for writing and then set it to the boolean value of True.

Public Sub Main()
   
Dim vars As Variables
   Dts.VariableDispenser.LockOneForWrite(
"AlertAdmin", vars)
   vars("AlertAdmin").Value = True
   Dts.TaskResult = Dts.Results.Success
End Sub

Method 2

The second method is typically what I would recommend using just for simplicity. In this method, you would open the Script task and before clicking on Design Script, you must ReadOnlyVariables and ReadWriteVariables options. If you have more than one variable you wish to be available in the Script task, you can seperate them with commas. If you do not set this, you will not see an error at design time but at run time, you'll receive the error shown below: 

The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.

at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index)

at ScriptTask_8693feb81f2d4b7b8a26ae87a8a5f960.ScriptMain.Main() in dts://Scripts/ScriptTask_8693feb81f2d4b7b8a26ae87a8a5f960/ScriptMain:line 19

With the variables now being passed in, you can perform the same type of action as I showed in the earlier script in a single line (the second line shown below). The locking is done by the Script task UI.

Public Sub Main()
   Dts.Variables(
"AlertAdmin").Value = True
   Dts.TaskResult = Dts.Results.Success
End Sub

The ReadOnlyVariables and ReadWriteVariables options are available to you in the Expressions tab too if the variable name that you wish to pass in is unknown or dynamic. There are other ways to set the variables of course. Most tasks have hooks into the variables like the Execute SQL task. Hope this helps!

-- Brian Knight