Post reply

How to pass a parameter to a package

  • September 17, 2009 at 4:42 am

    #138871

    Currently,there is a package which uses the import export wizard in sql server(running an Oracle query against the Oracle server)

    to obtain the data from Oracle.

    My requirement will be to run a SP to get the max of the values in a table in sql server.Then pass this max value to the package (Oracle query),

    so that I can filter out the records to be imported from Oracle db.

    Can I do something like this using the package.

  • September 17, 2009 at 7:25 am

    #1054959

    1. Create an SSIS Package with Execute SQL Task (Create a SSIS variable of datatype 'object' - call it SQLParam)

    2. Add your stored procedure to it (EXEC StoredProcName @Param or EXEC StoredProcName ? based on your connection)

    3. If you want the get the results of the select into a variable, go to the ResultSet tab of the execute sql task and add a resultset (name it 0) and assign it to the variable SQLParam you created earlier.

    4. Create an Execute Package Task in the same package, reference it to the other package (the import export)

    5. Open the Import Export package, create a package configuration of type - Parent package variable and name it SQLParam and reference it to a variable in the ImportExport package.

    This should make the Import Export package read the parent package variable and use it according to your logic.

    Hope this helps.

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

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