Very Computer

Dear All

I want within a VB DTS custom task to read from a specified global
variable as well after runtime to modify the global variable values
can you please point me where I can get a sample which shows this
feature.

Many thanks
Belinda

You want to read a GV value after the package has finished executing and
been destroyed. ?

In this instance I would store the final result of the GV to more permanent
media (File, DB Table)

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

Quote:> Dear All

> I want within a VB DTS custom task to read from a specified global
> variable as well after runtime to modify the global variable values
> can you please point me where I can get a sample which shows this
> feature.

> Many thanks
> Belinda

Quote:>You want to read a GV value after the package has finished executing and
>been destroyed. ?

>In this instance I would store the final result of the GV to more permanent
>media (File, DB Table)

I'm a bit confused by what you want too.

You can add, edit remove global variables in two places within a custom
task-

- at design-time through a UI form
- at run-time in the custom task execute method

Can you try and explain further.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

Hi Darren

I know how to use global variables. The problem I have is as follows:

1. I have written a custom VB task.
2. Within this custom VB task I have written I want to access the global variables
within the package to be precise within the custom task in VB I want to read the
value in a global variable defined in the package and to set value for global
variables defined in the package.

My problem is I have not seen any samples of custom DTS VB tasks that show how
to read a package global variable or set a package global variable. I want to
return the results of the custom task back to the package into a global variable.

So all I need is a sample VB custom task code that shows how to read a package
global variable and set a package global variable.

Thanks
Belinda

<snip>

Quote:

>So all I need is a sample VB custom task code that shows how to read a package
>global variable and set a package global variable.

The execute method gives you a reference to the package, so just read of
from the global variables collection-

Private Sub CustomTask_Execute(ByVal pPackage As Object, ByVal
pPackageEvents As Object,  ByVal pPackageLog As Object, pTaskResult As
DTS.DTSTaskExecResult)

    On Error GoTo Err_Handler

    Dim oPkg As DTS.Package

    Set oPkg = pPackage
    Set pPackage = Nothing

   Dim sString as String

   ' Set a GV
   oPkg.GlobalVariables("GlobalVariableName").Value = "Something"

   ' Read a GV
   sString = oPkg.GlobalVariables("GlobalVariableName").Value

End Sub

As documented in Books Online, GlobalVariables is the global variable
collection, made of up 0 or more GLobalVariable objects, which have
amongst other properties a Name and a Value. As a collection you can
enumerate all variables, or reference by ID or Name.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

Hi Darren

Many thanks for your time. The code segment worked perfectly except
that despite invoking the package the global variable appears to be
modified by the custom task to "something" and after the task
execution when I look at package properties I see that the value of
the global variable as been changed to "something" and I save the
package I rerun the package still the custom task shows me the same
old initial value of the global variable not the current one I see in
the package properties. Not sure why this is happening I want the
value set by the custom task to be persistent in the global variable.

Also can you kindly post the code to access the entire global
variables package collection.

Thanks & regards
Belinda

<snip>

Quote:

>Hi Darren

>Many thanks for your time. The code segment worked perfectly except
>that despite invoking the package the global variable appears to be
>modified by the custom task to "something" and after the task
>execution when I look at package properties I see that the value of
>the global variable as been changed to "something" and I save the
>package I rerun the package still the custom task shows me the same
>old initial value of the global variable not the current one I see in
>the package properties. Not sure why this is happening I want the
>value set by the custom task to be persistent in the global variable.

>Also can you kindly post the code to access the entire global
>variables package collection.

>Thanks & regards
>Belinda

Belinda, I am confused by all the somethings and what is expected where
in your description. Here is a some code you can paste into an ActiveX
Scrip Task that will enumerate all GVs and write them to a file which
may be useful-

Function Main()
        Dim oFSO, oFile, oGlobalVariables, oVariable
        Set oFSO = CreateObject("Scripting.FileSystemObject")
        Set oFile = oFSO.CreateTextFile("C:\GVWrite.txt", True)

        oFile.WriteLine("Global Variables for Package """ &
DTSGlobalVariables.Parent.Name & """ " & Now())

        For Each oVariable In DTSGlobalVariables
                oFile.WriteLine("Name: """ & oVariable.Name & """" &
vbCrLf & "Value: """ & oVariable.Value & """")
        Next

        oFile.Close

        Main = DTSTaskExecResult_Success
End Function

To use the same core code in a custom task change DTSGlobalVariables to
oPkg.GlobalVariables

Using this type of logging does it make it any clearer what is going on?

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

How may we use "oPkg.GlobalVariables("GlobalVariableName").Value" in
the created DTS package in a SQL task?

Rob

Quote:>How may we use "oPkg.GlobalVariables("GlobalVariableName").Value" in
>the created DTS package in a SQL task?

>Rob

You can use the global variable called GlobalVariableName in the example
as you would any other global variable.

The sample just illustrated how to change an existing global variable
value, or read an existing global variable value.

To use a global variable value, regardless of the use of custom tasks,
you need to add a place holder, a question mark (?), into your SQL where
you wish to use the parameter. Then click the "Parameters" button below
the SQL code window and map the place holder to a global variable .

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com