SQL Server Performance

Help wanted with understanding an ActiveX script

Discussion in 'SQL Server DTS-Related Questions' started by jaybee, Nov 21, 2007.

  1. jaybee New Member

    Hi all,

    Background- I've got an academic package the following script is part of thatuses lookups to extract row-based sales data from Access, maps columnsusing values from the first row of an Excel spreadsheet (source), salestargets

    I've highlighted in bold the areas I'm struggling to follow (as a pure Admin guy!),

    Function Main()
    Dim i

    DTSDestination("Product") = DTSSource("Product")

    'Load Actual sales data for each month
    For i = 1to DTSGlobalVariables("ActualsThroughMonth").Value
    DTSDestination(i+1) = DTSLookups("GetActualsCurrentMonth").Execute(DTSSource("Product"),i, DTSGlobalVariables("CurrentYear").Value)
    DTSDestination("Full Year") = DTSLookups("GetActualsYTD").Execute(DTSSource("Product"), DTSGlobalVariables("ActualsThroughMonth").Value),DTSGlobalVariables("CurrentYear").Value)
    Main = DTSTransformStat_OK
    End Function

    I'm not sure what the "For i = 1" is about, seems like some programming procedure I'm not aware of.

    Execute(DTSSource("Product"), don't know why this is a source, and is the word 'product' in speech marks because there are numerous products?

    Let me know if more info needed. I'll be glad to get this one cleared up, it's bugged me a long time now.

  2. martins New Member

    The For-loop is purely there to loop through the columns of your destination. The "i" is only a variable that gets incremented to facilitate the population of the columns. For instance, when i=1 (first iteration) the second column (i + 1) in your spreadsheet will be populated.
    The DTSSource("Product") means that as a first parameter for your lookup (the name of the lookup is "GetActualSYTD"), it will pass the value of the product column it obtained from your source (Access table).
    Hope this helps.
  3. martins New Member

    To clarify some more...
    The "Next" keyword at the end of the For-loop will increment the variable called "i". In some languages it will read "Next i" instead of just "Next", which makes it a bit easier to understand.

Share This Page