SQL Server Performance

DTS Problem with Global Variables

Discussion in 'SQL Server DTS-Related Questions' started by link3228, Nov 25, 2003.

  1. link3228 New Member

    I have a group of DTS packages that I want to use with multiple databases. My problems is when I change the database Global variables the Data Driven Queries have the following problems:
    1) The bindings default to the first table in the database.
    2) After I change to the correct database the parameter values in the queries appear to resequence to the order of fields in the database.

    I still haven't found a way to change the database for Transform Data Tasks. They appear to stay the same.

    Thanks in advance for your help.

    John Shaening

    John Shaening
  2. satya Moderator

    May search thruhttp://www.sqldts.com website for any reference/code example in this regard.


    Satya SKJ
  3. Dave Wells New Member

    Here's some sample code that works. Might not be the nicest way to do things but it does the job.

    Function Main()

    ' Change DataPump Destinations
    ' Easier to do this here than with Dynamic Properties
    DTSGlobalVariables.Parent.Tasks.Item("NameOfDataPump").CustomTask.DestinationObjectName= "[" & DTSGlobalVariables("DestinationDBVariableName").Value & "].[dbo].[TableName]"

    'Change DataPump Source
    DTSGlobalVariables.Parent.Tasks.Item("NameOfDataPump").CustomTask.SourceObjectName= "[" & DTSGlobalVariables("SourceDBVariableName").Value & "].[dbo].[TableName]"

    Main = DTSTaskExecResult_Success

    End Function

Share This Page