locked
SQL Integration Services SSISDB and Paramters RRS feed

  • Question

  • I have a VS Package written in VS 2010.  This package has a Parameter which I want passed from an Environment Variable.  I've deployed the package file to SSISDB, in addition, I've created the Environment Variables and updated the package to use the Variables.  I can manually execute the package from within Mgmt. Studio but what I really need is to be able to call the package via sql similar to this:

    begin
     declare @output_execution_id bigint
     declare @execution_id bigint
     declare @ServiceCallNbr char(11)
    set @ServiceCallNbr = '0000060004'
    exec catalog.create_execution  'FieldService','ServiceCall', 'ServiceCalleMail.dtsx',NULL, 0,@execution_id out
    exec catalog.set_environment_property 'FieldService', 'FieldServiceCalleMail', 'Project_ServiceCall', @ServiceCallNbr
    --exec catalog.set_environment_property 'FieldService', 'ServiceCallParameters', 'DESCRIPTION', 'Test'
    --exec catalog.set_execution_parameter_value  @execution_id, 20, 'Package_ServiceCall',@ServiceCallNbr
    exec SSISDB.catalog.start_execution @execution_id
    set @output_execution_id = @execution_id
    end

    The problem though when I run the above is that I get the following error:

    Msg 27101, Level 16, State 1, Procedure set_environment_property, Line 116
    The value specified for the input parameter, 'DESCRIPTION', is not valid. Provide a valid value for this parameter.

    DESCRIPTION is not even a parameter in the package or the environment variables.  Any ideas where/why it wants a description for the environment?
    Tuesday, August 13, 2013 2:31 AM

Answers

  • You need to dig in deeper into the MSDN pages :)

    catalog.set_execution_parameter_value

    Use the value 20 to indicate a project parameter or the value 30 to indicate a package parameter.

    You used the value 50, which is reserved for a certain set of system parameters.


    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    • Marked as answer by doddsw916 Tuesday, August 13, 2013 1:52 PM
    Tuesday, August 13, 2013 1:46 PM

All replies

  • From MSDN:

    In this release, only the Description property can be set. The property value for the Description property cannot exceed 4000 characters.

    catalog.set_environment_variable_property

    You are most likely using the wrong stored procedure.
    You need catalog.set_environment_variable_value.



    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Tuesday, August 13, 2013 6:45 AM
  • Looks like that got me past the DESCRIPTION issue.  I went ahead and made the following changes...

    begin
     declare @output_execution_id bigint
     declare @execution_id bigint
     declare @ServiceCallNbr char(11)
    set @ServiceCallNbr = '0000060004'
    exec catalog.create_execution  'FieldService','ServiceCall', 'ServiceCalleMail.dtsx',NULL, 0,@execution_id out
    exec catalog.set_environment_variable_value 'FieldService', 'FieldServiceCalleMail', 'Project_ServiceCall', @ServiceCallNbr
    exec catalog.set_execution_parameter_value  @execution_id, 50, 'Package_ServiceCall', @ServiceCallNbr
    exec SSISDB.catalog.start_execution @execution_id
    set @output_execution_id = @execution_id
    end

    The error that I get now is related to the execution_parameter_value and it states that the 'Package_ServiceCall' doesn't exist or I don't have sufficient permissions.  My user is assigned 'ssis_admin' role in sql.  If I comment out the execution_parameter_value code then I get an error that the parameters need to be set.

    Any ideas on what I'm missing?

    Tuesday, August 13, 2013 1:36 PM
  • You need to dig in deeper into the MSDN pages :)

    catalog.set_execution_parameter_value

    Use the value 20 to indicate a project parameter or the value 30 to indicate a package parameter.

    You used the value 50, which is reserved for a certain set of system parameters.


    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    • Marked as answer by doddsw916 Tuesday, August 13, 2013 1:52 PM
    Tuesday, August 13, 2013 1:46 PM
  • That worked, I changed the value to 30 and now it runs!  Thank you.
    Tuesday, August 13, 2013 1:52 PM