Error message: Script task, For loop container

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Error message: Script task, For loop container

Author

Topic

pssheba
Yak Posting Veteran

95 Posts

Posted - 2008-04-23 : 08:00:59

I everyone,
I'm learning SSIS from a book but the most simple example is not working.
The "For each task" I assigned "xyz" as name.
I added a variable onto the "variables" window and called it "counter" Its scope was set to "xyz".
Inside the "for loop" ("xyz") editor i assigned values as follows:
"initexpression"-"@counter=0"
"eval expression"-"@counter<5"
"assign expression"-"@counter=@counter+1"

I added "Script task" onto the "For each" container and into the "Script task editor" I assigned
the following values:
"ReadOnlyVariables"-"counter"
Its design script is set to:


Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain
    Public Sub Main()
        Dim variables As Variables
        If Dts.Variables.Contains("Counter") = True Then
            Dts.VariableDispenser.LockOneForRead("Counter", variables)
        End If

        MsgBox("You are in iteration: " & CStr(variables("Counter").Value))
        Dts.TaskResult = Dts.Results.Success

    End Sub
End Class

Runnig it i get the following error message:

quote:

"DTS Script task has encountered an exeption in user code:
Project name:ScriptTask_e7d98dbad0de4041bcdc9079a5c2fa65
Object reference not set to an instance of an object.."

The line where the error occurs is from within the above script:


"MsgBox("You are in iteration:" " & CStr(variables("Counter").value))

Anyone understands what that means? What is "object reference" and how do i set it to an instance of an object?
Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-23 : 08:18:56

Dim variables As Variables
Dim strCounter as string
Dts.VariableDispenser.LockOneForRead("COunter", variables)
strCOunter = variables("Counter").Value.ToString()
variables.Unlock()

have a look at
http://www.simple-talk.com/sql/sql-server-2005/quick-tip-performing-an-ftp-rename-in-a-ssis-script-task/

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pssheba
Yak Posting Veteran

95 Posts

Posted - 2008-04-23 : 08:42:40

Hi nr,
Thanks. The new script looks now like:


Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain
    Public Sub Main()
        Dim variables As Variables
        Dim strCounter As String
        Dts.VariableDispenser.LockOneForRead("Counter", variables)
        strCounter = variables("Counter").Value.ToString()
        variables.Unlock()
        If Dts.Variables.Contains("Counter") = True Then
            Dts.VariableDispenser.LockOneForRead("Counter", variables)
        End If

        MsgBox("You are in iteration: " & CStr(variables("Counter").Value))
        Dts.TaskResult = Dts.Results.Success

    End Sub



End Class

And the new error message is:

quote:

Failed to lock variable "Counter" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-23 : 08:52:09

The error message is fairly self explanatory.
Maybe due to the scope - I always create varables with package scope.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pssheba
Yak Posting Veteran

95 Posts

Posted - 2008-04-23 : 09:10:53

quote:
Originally posted by nr

The error message is fairly self explanatory.
Maybe due to the scope - I always create varables with package scope.

How do you change scope ? The scope (xyz) was assigned by the system and i dont see how i can set it intp "package".Besides, "xyz" is the container, there is no more scope behind the container.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources