pass parameters via DTS ?

  • How can I get the LocalPackage to allow

    parameters so I can do the following ?

    1. Click green "Run" button

    2. Enter the "StartNumber"

    3. Wait for the job to finish

    ------------------------------------

    Current Setup

    1. Open DTS

    2. add a "Transform Data Task"

    3. click "Properties"

    4. Add the following SQL statement

    SELECT ITEMNO, DESCRIPTION, STATUS, CATEGORY, AVAILABLEINV

    FROM ITEMMAST

    WHERE (SUBSTRING(ITEMNO, 1, 5) <> '00000')

    ORDER BY ITEMNO

    5. Click green "Run" button

    6. The job will work

    7. Modify the SQL statement as follows

    Declare @StartNumber

    SELECT ITEMNO, DESCRIPTION, STATUS, CATEGORY, AVAILABLEINV

    FROM ITEMMAST

    WHERE (SUBSTRING(ITEMNO, 1, 5) <> @StartNumber )

    ORDER BY ITEMNO

    8. Receive a "invalid statement" error

  • Add an activeX task that runs at the start of your package.  In the task, use an inputbox to prompt for the start number. 

    I assume that you want to use this value instead of "0000" in the first select statement.  You can then assign the value to the select statement from the same activex task.

    e.g.

     

    Function Main()

     Dim SQL

     Dim oPkg, oTask, oConnection

     Set oPkg = DTSGlobalVariables.Parent

    x = InputBox ("Enter the start number")

     For Each oTask In oPkg.Tasks

      If Instr(oTask.Name, "DataPump") > 0  And oTask.Description  = "Some meaningful description" Then

       oTask.Properties("SourceSQLStatement").Value = "SELECT ITEMNO, DESCRIPTION, STATUS, CATEGORY, AVAILABLEINV FROM ITEMMAST WHERE (SUBSTRING(ITEMNO, 1, 5) <> '" & _

      cstr(x) & _

      "')  ORDER BY ITEMNO"

     End If

     Next

     Main = DTSTaskExecResult_Success

    End Function

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

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