Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    4

    Unanswered: DTS Package Date Parameter

    Hi

    I'm using a DTS package to move data from one database to another, as you do.
    The Transform Data task between the two connections uses a SQL statement which includes a hardcoded date variable in the criteria.
    " where entrydate = '31 Oct 2003' "

    Is there anyway to pass a parameter or variable to the dts package, like you can to a stored procedure?

    I thought of having another statement to get a datevalue from another table, but DTS doesn't seem to like multiple sql statements in the Transform Data Tasks.
    My last resort option is to use the getdate() function, but this would reduce my flexibility too much.

    Thanks
    Dan

  2. #2
    Join Date
    Sep 2003
    Location
    New York, NY
    Posts
    136
    you can use global variable in the DTS package. One step in the package can save date in this variable and then the transform data task can read that.

    activex syntax to read the value:
    var_value = DTSGlobalVariables("DTS_var").value

  3. #3
    Join Date
    Oct 2003
    Posts
    4
    Theres a couple of steps for this I don't understand.
    Should the tasks in the process be as follows?:
    1: ActiveX script task to
    get the date value from a table using sql, and saves it as a globalvariable 'Var1'?
    2: Transform Data task which uses global variable 'Var1' in the sql statement?

    If so:
    How do you use sql in the ActiveX task?
    And how do you reference the global variable in the Transform data sql statement?

  4. #4
    Join Date
    Sep 2003
    Posts
    212
    In the transformation use activex script and run the sql in activex and assign the result of the sql to the DTSDestination("Col1").

  5. #5
    Join Date
    Sep 2003
    Posts
    212
    Originally posted by vmlal
    In the transformation use activex script and run the sql in activex and assign the result of the sql to the DTSDestination("Col1").
    run sql in activex:

    Dim oSQLServer
    Dim strSQLServer
    Dim Query
    Dim Messages

    Set oSQLServer = CreateObject("SQLDMO.SQLServer")
    strSQLServer = "ServerName"
    ' use integrated sec and connect
    oSQLServer.LoginSecure = TRUE
    oSQLServer.Connect strSQLServer

    strQuery = "select col1,col2 from blah"
    'Run the query
    set Query = oSQLServer.ExecuteWithResults(strQuery)
    Messages = Query.GetColumnString(1,1) & " " & Query.GetColumnString(1,2)

  6. #6
    Join Date
    Oct 2003
    Posts
    4
    I've got a lot to learn about ActiveX for DTS, thanks for pointing me into the right direction.

    Thanks for your help rohitkumar and vmlal!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •