Update global variable in DTS from a value in a table

SQL Server 2000
Here's what i want to do:

I have a DTS with some global variables.  I would like to have the global variables updated from a table in a database (on the same server)...for example the table may be named "file locations" and the table then has a "name-value" pair.

Currently we do something like create an ActiveX script to get the value like the following:

'  Visual Basic ActiveX Script

Function Main()
      dim test
      Main = DTSTaskExecResult_Success

      dim myConn
      dim myRecordset
      dim iRowCount

      ' instantiate the ADO objects
      set myConn = CreateObject("ADODB.Connection")
      set myRecordset = CreateObject("ADODB.Recordset")

      myConn.Open = "Provider=SQLOLEDB.1;Data Source=Workstation1234; Trusted_Connection=yes;database=DataPrep;"

      mySQLCmdText = "Select value from DataPrep.dbo.dbproperty where name ='SettingsFile'"

      myRecordset.Open mySQLCmdText, myConn
      set Flds = myRecordset.Fields
      set iRowCount = Flds("value")

      msgbox "Current RCDR: " & iRowCount

      Main = DTSTaskExecResult_Success
End Function


Then we set the global variable with a "Dynamic Properties task" object in the dts (on succcess of the get)


First of all that seems like a lot of work to get the value from a table in a server that we're already on...is there a leaner way?

If not is there a way where i can then put this code in a user defined function in the database itself and call that function within the dts?  The thing is we have maybe 10 DTSs and everytime one would need to be updated - it would require 10 updates.

I looked at : http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_addf_ax_06er.asp  and it seems that's what MS is doing...hmmm.

As far as points go, I'll start at 200 points if someone can point me to where this is already answered.  If it involves someone doing actual work on this I will significantly increase it - at least more than double the 200 currently.

Who is Participating?
You're right is it a lot of work.

You can use a "dyamic properties task" to load the values in:

It looks like a box with things poking out of it.

1. Drop it on the page.
2. Press Add to add a definition
3. Navigate to the ("@SettingsLoca global variable
4. Click the 'Value' property and press the Set button
5. Set the 'Source' to Query
6. Type in your query
7. Press 'Parse' and chuckle at the message that is returned - (disregard it!)
8. Press OK
9. Now right click your task and make sure it loads the variable OK.

You can do basically the same thing with an Execute SQL task:

1. Put this in the SQL pane:
     Select value from DataPrep.dbo.dbproperty where name ='SettingsFile'
2. Press the parameters button and go to the output parameters tab
3. Pick output parameter type = 'Row Value' and fill in the output parameter values.

I find this way a little easier becuaseyuo can preview the SQL.

Here is way of doing it with stored procedures:


This is a very handy site in general.
mchandlerAuthor Commented:

That's exactly what i was looking for...I will award you the points, but have another question....need to go to a meeting will try to get back here by end of day...thanks.

mchandlerAuthor Commented:
figured everything out...good to go, thanks!!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.