Post reply

Dynamic DTS

  • September 14, 2004 at 9:53 am

    #59422

    Hi,
     
    I need to create a dynamic DTS that depending on a parameter (either file or query) will:

    • The parameters is the last day of the a month (YYYYMMDD).

    • Assign this value to a Global Variable GV1.

    • Assign to another Global Variable GV2 the month (YYYYMM).

    • Use the value of GV1 in a query to be inserted set the destination

    • Use the value of GV2 to dynamically set the destination table of a Pump task ("Database"."Owner"."Table_prefix_" + GV2).

    • Use the value of GV1 in a query to be inserted in the destination table.

    If have tried to to this, but I am not able to set GV2 and consequently the destination table.
     
    Thanks in advance.

  • September 15, 2004 at 7:00 am

    #523150

    Don't know if I am reading your post correctly or not, but assume you need something like this in the activex task -

    DTSGlobalVariables("gv1").value = Month(DTSGlobalVariables("gv2").value)

     

    Hope it helps.

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

  • September 15, 2004 at 8:34 am

    #523175

    I was already able to assign GV2.

    The problem was that GV1 is of type int and GV2 is of type "string".

    Is was trying to do GV1/100 to remove the DD part. Then I converted GV1 to a string and selected the left 6 characters.
     
    Now, I have a problem with the Transform Data Task (usign a select as the source, not a table or view), the query has 8 parameters (1 time GV1 and 7 times GV3). The error message is "Parameter type is not supported". Both variables GV1 and GV3 are of type int and in the select the expected value in the query all parameters are of type int in the source.
     
    Does this mean that I cannot use parameters in the select of the Transform Data Task? Or does it mean that there is a incompatibility of the parameters and the query? E.g., I already forced a convert of the parameters to int and the error message is the same.

  • September 15, 2004 at 9:14 am

    #523183

    This should do it...

    ' Assuming GV1 is formatted as YYYYMMDD
Dim sz_month, sz_dest, o_task
sz_month = Left(DTSGlobalVariables("GV1"), 6)
sz_dest = "[Database].[Owner].[Table_Prefix_" & sz_month & "]"
    ' If you don't know the actual name of the task, right-click in the DTS Editor and select
' "disconnected edit", expand the "Tasks" tree and find the one you're looking for.
' In the right-hand pane, you'll find the "Name" property.
' It's usually something like: DTSTask_DTSDataPumpTask_#
' Replace the TASK_NAME place holder in the assignment below
    Set o_task = DTSGlobalVariables.Tasks("TASK_NAME")
o_task.Properties("DestinationObjectName").Value = sz_dest
DTSGlobalVariables("GV2") = sz_month

  • September 15, 2004 at 9:26 am

    #523189

    My post must have executed the same time as your followup.

    If you're using TSQL as your source of the data pump task, you can still follow the examples above.  Dig around in the Disconnected Editor to see the properties available to you.

    To build on my previous post, you could convert all your Int types to String prior to using any string functions.

    sz_GV1 = CStr(DTSGlobalVariables("GV1"))

    Then use the Left() function on the string var.

    Then, dynamically build your SQL statement using your variables & gVariables.

    Finalize it with:

    o_task.Properties("SourceSQLStatement") = sz_sql

    Hope this helps.

    --b

Viewing 5 posts - 1 through 4 (of 4 total)

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