Globle variables in DTP package

  • Somebody

    SSC Eights!

    Points: 928

    Hello all,

    HOpe that someone can help me with this.  If there is a solution for this, my life will be much easier. And thank you everyone very much for reading my thread and trying to help me!

    I have a package to load data into production.  Normally, We have to run this package manually by providing some globle variables ( to feed some store procedures in the package),depending on what kind of data we are loading.  Now, there is a load of data that I have to load into production daily by using that package.  In order to load, I have to create a file in a correct format, so I created another package to do this task.  What I want to do now is to put these two packages ( the one I created to produce the data file and the one that used to load the data in this file into production) into one package, so I can automate the process.

    The problem is that I dont know how I can provide globle variables for the package that loads data into production.  I can't hardcode or modify anything to that package becuase it is used by many people not just me.  Is there any way that I can provide globle variables for the loading package so I can automate the whole process?  Please help!

    Thank you so much in advance!


  • Nicholas Cain


    Points: 33009

    I would look at the DTSRUN command in BOL. You are able to pass global variables into the package at runtime using switches within the DTSRUN.

    Shamless self promotion - read my blog

  • Jonathan Stokes

    SSCrazy Eights

    Points: 9861

    Rather than messing about with two packages, I find it easier to execute dynamic dts packages by using vba.

    Copy this code into a vba module on a word document or excel etc, and change the packagename, servername and the value of the variables. All upper case in the script so that they stand out.


    Public Function fnExecPackage()   

    On error goto err_handler

        Dim oPKG As New DTS.Package

        Dim oPKGname As String

        Dim DidErrorOccurSomewhere as string

        Dim oServerName as string

        oPKGname = "YOURPACKAGENAME"

        oServerName = "YOURSERVERNAME"

        DidErrorOccurSomewhere = "N"

     oPKG.LoadFromSQLServer oServerName, , , _

            DTSSQLStgFlag_UseTrustedConnection, , , , oPKGname ' connect to the package

     oPKG.GlobalVariables(1).Value = "YOURVALUE1" ' set value of first variable in the sequence

     oPKG.GlobalVariables(2).Value = "YOURVALUE1" ' set value of second variable in the sequence

            ' Set Exec on Main Thread as VB is apartment threaded and DTS is free threaded

            For Each oStep In oPKG.Steps

                oStep.ExecuteInMainThread = True


            oPKG.Execute ' execute the package

            ' check for errors from step/s

            For Each oStep In oPKG.Steps

                If oStep.ExecutionResult = DTSStepExecResult_Failure Then

                    DidErrorOccurSomewhere = "Y"

                End If


     If DidErrorOccurSomewhere = "Y" Then

      msgbox "error in a step/s", vbOkOnly

     End if

    Exit Function


        MsgBox "Error # " & Err & ": " & Error(Err)

    End Function



    Needs a bit of tidying up as I took it straight from some larger set of code I have. It will show a general error and an error in a step.

    You can just call the package from another package with basically the same code and set the value of the variables there as well. It's entirely a preference thing.

    The Users are always right - when I'm not wrong!

  • SueB


    Points: 1537


    I liked your code and it worked for me but when I try to do this from the computer of someone who is not a sysadmin it does not work.  Is a nonsysadmin able to run this?  If so, what permissions need to be set to do this?


  • Jonathan Stokes

    SSCrazy Eights

    Points: 9861

    Glad to have helped.

    The code above is using a trusted connection to connect sql server. You can specify a sql serer username and password in the connection string to achive this. Try this. I am not too sure about the syntax, but it should work for you. Not tested by myslef, though.

    Make sure the sql server user has the necessary permissions.

    oPKG.LoadFromSQLServer oServerName,"USERNAME" ,"PASSWORD" , _

            DTSSQLStgFlag_Default, , , , oPKGname ' connect to the package

    If you paste it into your module, you should be able to step throguh each paramater to make sure the username, password etc is correct.

    The Users are always right - when I'm not wrong!

  • BrenBart


    Points: 2622

    If you are using SQL Server 2000 you could also create a package and use the 'Execute Package Task'.  This task allows for the transfer of values between the inner (package you are trying to run) and the outer (package that contains the 'execute package task') packages.


    I would expect that the login that runs the code needs to have the same rights as one that can run the package.

    As for sysadmin vs non-sysadmin that all depends on what the package does.  i.e. Does it access files, other servers, objects with specific permissions etc.

    I forget exactly what this concept is called but if you are running the code on one server (say in a scheduled job) that kicks off a package on another server which in turn accesses a third server you can run into problems with login rights essentially not making the hops from one server to the next.

    In our environment we have a Windows network login that is used to run all MSSQL and SQLServerAgent services so we avoid the problem.  There are other ways around it but by the time we discovered it was a problem we would have had to make changes to all 45 of our SQL Servers and just used a different workaround.

    "I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
    (Memoirs of a geek)

  • jg4smile

    SSC Rookie

    Points: 33

    See thread: Can you set "INNER PACKAGE GLOBAL VARIABLES" through an Acti... for an example of changing Global Variables in an Execute Package Task.


Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply