locked
Pass parameter to SSIS RRS feed

  • Question

  • I have a SSIS package that imports (appends) data to a SQL table from an ODBC source.  This package is run from within a VS project.

    I'd like to pass a parameter to this package to limit the records it imports.  I have seen some discussion of this but I need a detailed step-by-step or a good working example.  Specifically:

    1.  How to get the parameter to the package to begin with.  The VB function that runs the SSIS only passes parameters to the stored procedure that executes the SSIS.

    2.  How to refer to the parameter value in the SQL statement of the data reader object in the SSIS package.  Instead of saying 'Select * from table1' it needs to say 'Select * from table1 WHERE field1 = @passedparam'

     

    Sunday, February 25, 2007 6:28 PM

Answers

  • I would add a SSIS variable to the package to hold the parameter you want to pass, let's say @PassedParam.

    I would assign the parameter value via dtexec command line; using the SET option.

    I would put the SQL Statement into another SSIS variable that is based in a expression (EvaluateAsExpression property in the variable equal to TRUE). That expression would use look at the @PassedParam variable.

     

    Let me know if this make sense...

    Monday, February 26, 2007 12:02 AM
  • Sorry, I answered my own question.  There is a good overview of dtexec with examples at http://msdn2.microsoft.com/en-us/library/ms162810.aspx .
    Monday, February 26, 2007 9:31 PM

All replies

  • I would add a SSIS variable to the package to hold the parameter you want to pass, let's say @PassedParam.

    I would assign the parameter value via dtexec command line; using the SET option.

    I would put the SQL Statement into another SSIS variable that is based in a expression (EvaluateAsExpression property in the variable equal to TRUE). That expression would use look at the @PassedParam variable.

     

    Let me know if this make sense...

    Monday, February 26, 2007 12:02 AM
  • Thank you.  This makes conceptual sense, but I am unfamiliar with dtexec.  Does the app have to shell to the OS to run this?  Do you have an example?
    Monday, February 26, 2007 9:21 PM
  • Sorry, I answered my own question.  There is a good overview of dtexec with examples at http://msdn2.microsoft.com/en-us/library/ms162810.aspx .
    Monday, February 26, 2007 9:31 PM