Dts.VariableDispenser.GetVariables(vars) is not ge

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)

Dts.VariableDispenser.GetVariables(vars) is not ge

Author

Topic

fredong
Yak Posting Veteran

80 Posts

Posted - 2009-02-27 : 15:31:59

Hi
I am using SSIS 2005 Scrip task on VB.NET and I am trying to pass a value to my input variable thru a function but it seemed like is not capturing the value. Whether I use User:: with a prefix or FullPath is not taking it. There is no error and is not capturing the value in the Variables box. I did debug it for a long time and narrow down it to the Dts.VariableDispenser.GetVariables(vars) is not getting value from varValue. Below is my code. Am I missing something? Please advise. Thanks.

' Microsoft SQL Server Integration Services Script Task

' Write scripts using Microsoft Visual Basic

' The ScriptMain class is the entry point of the Script Task.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

' The execution engine calls this method when the task executes.

' To access the object model, use the Dts object. Connections, variables, events,

' and logging features are available as static members of the Dts class.

' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

'

' To open Code and Text Editor Help, press F1.

' To open Object Browser, press Ctrl+Alt+J.

Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)

MsgBox("In wrtie()")

MsgBox(varValue)

Try

Dim vars As Variables

Dts.VariableDispenser.LockForWrite(varName)

Dts.VariableDispenser.GetVariables(vars)

MsgBox("after vars")

Try

vars(varName).Value = varValue

Catch ex As Exception

MsgBox("in inner catch")

MsgBox(E.ToString)

Throw ex

Finally

vars.Unlock()

End Try

Catch ex As Exception

MsgBox("in outer catch")

MsgBox(E.ToString)

Throw ex

End Try

End Sub

Public Sub Main()

'

' Add your code here

Dim strFolder As String

Dim strFilename As String

Dim strTomorrow As String

Dim strNewFullPath As String

Dim objMain As New ScriptMain

'Dim FullPath As String

'do path in script

strFolder = Dts.Variables("User::FolderName").Value.ToString

strFilename = Dts.Variables("User::FileName").Value.ToString()

strTomorrow = CStr(Format(Today().AddDays(+1), "yyyyMMdd"))

strNewFullPath = strFolder & strFilename.Substring(0, strFilename.IndexOf(".txt")) & "_" & strTomorrow & ".txt"

'display new value of path

'MsgBox(strNewFullPath)

Dts.Variables("User::FullPath").Value = strNewFullPath

MsgBox(Dts.Variables("User::FullPath").Value)

objMain.WriteVariable("User::FullPath", strNewFullPath)

'WriteVariable("FullPath", )

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

k

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources