Update global variable in DTS from a value in a table

Posted on 2006-05-18
Last Modified: 2012-06-22
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 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 :  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.

Question by:mchandler
    LVL 30

    Accepted Solution

    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.
    LVL 1

    Author Comment


    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.

    LVL 30

    Expert Comment

    LVL 1

    Author Comment

    figured everything out...good to go, thanks!!!

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now