none
SSIS 2012 Set Project.Params RRS feed

  • Question

  • I want to set a parameter but I just can figure out how, in previous version, you could update the config table with a sql statement and the job was done...

    I want to store the current date in a parameter so I can use it the next time as my startdate. If anybody could tell me how you must do this with parameters?

    I do not want to set a variable in each package.

    Thanks in advance.


    • Edited by BramDe Monday, September 17, 2012 1:33 PM
    Monday, September 17, 2012 1:02 PM

Answers

All replies

  • We use package logging and it when you define it in SSIS it will generate a table in the database where the logging is enabled. You should be able to find the last run date from the columns it generates...

    SELECT  [id]
          ,[event]
          ,[computer]
          ,[operator]
          ,[source]
          ,[sourceid]
          ,[executionid]
          ,[starttime]
          ,[endtime]
          ,[datacode]
          ,[databytes]
          ,[message]
      FROM [DatabaseNameGoesHere].[dbo].[sysssislog]

    Tim

    Monday, September 17, 2012 1:10 PM
  • Yes Tim, there are different ways to store these values, but my question is, how do you put it in a parameter?

    With SSIS configurations, you can overwrite your value. With Parameters, you cannot overwrite your parameters....

    Monday, September 17, 2012 1:15 PM
  • I want to set a parameter but I just can figure out how, in previous version, you could update the config table with a sql statement and the job was done...

    I want to store the current date in a parameter so I can use it the next time as my startdate. If anybody could tell me how you must do this with parameters?

    I do not want to set a variable in each package.

    Thanks in advance.


    If you add a database table based configuration to the package (SSIS -> Configuration, use the Wizard to add it). Then you can also store and retrieve the value for the package parameter from there. Obviously, you can update the value in this table from either inside the package or outside using Execute SQL Task.

    See some help on how to make such a configuration set: http://mikedavissql.com/2011/10/04/using-configuration-tables-in-ssis-20082005/


    Arthur My Blog

    Monday, September 17, 2012 1:37 PM
    Moderator
  • I want to set a parameter but I just can figure out how, in previous version, you could update the config table with a sql statement and the job was done...

    I want to store the current date in a parameter so I can use it the next time as my startdate. If anybody could tell me how you must do this with parameters?

    I do not want to set a variable in each package.

    Thanks in advance.


    You can change a parameter value via a call to [SSISDB].[catalog].[set_execution_parameter_value]. Is that what you're after (I may have misunderstood)?

    ObjectStorageHelper<T> – A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson

    Monday, September 17, 2012 1:53 PM
    Moderator
  • Hi Arthur,

    Thanks for the response and this is true if you are using the Package Deployment Model.

    But we want to use the Project Deployment Model which uses parameters and not the configuration as we knew it in previous versions (correct me if I'm wrong).

    The solution you described above is how I use to do it, but with 2012 I would like to work with the Project Deployment Model as it has a lot of potential.

    I was thinking of calling my main package via T-SQL so I can run a query to get this LastLoadedDate and then pass it via a parameter but this won't be possible in all situations for example when running the ETL via dtexec when using another scheduling tool then SQL Agent.

    or via a SQL Task in the main package getting the LastLoadedDate and pass it on via a variable to the child packages, but this seems all to complicated for each what seems to be easy...

    Monday, September 17, 2012 1:58 PM
  • Yes, as Jamie said, your solution is in using set_execution_parameter_value stored procedure then.


    Arthur My Blog

    Monday, September 17, 2012 2:21 PM
    Moderator
  • Hi Jamie,

    Thanks it looks nice, but I cannot change this at runtime:

    Execute
    SQL Task:Error: Executing the query "exec
    [SSISDB].[catalog].[set_execution_parameter_v..." failed with the
    following error: "The parameter value cannot be changed after the
    execution has been started.".

    I want to find a way so that all my packages in the project can use this updated value.

    I save my value in a custom table, and want to use this value the next time I run my ETL in all packages.

    Thanks in advance.

    Monday, September 17, 2012 3:03 PM
  • Hi Jamie,

    Thanks it looks nice, but I cannot change this at runtime:

    Execute
    SQL Task:Error: Executing the query "exec
    [SSISDB].[catalog].[set_execution_parameter_v..." failed with the
    following error: "The parameter value cannot be changed after the
    execution has been started.".

    I want to find a way so that all my packages in the project can use this updated value.

    I save my value in a custom table, and want to use this value the next time I run my ETL in all packages.

    Thanks in advance.

    Correct. [SSISDB].[catalog].[set_execution_parameter_value] must be called before the execution begins. I don't understand why this means you can't use it though - simply get the value from your custom table and pass that value into [SSISDB].[catalog].[set_execution_parameter_value].

    Sorry, I'm not understanding the problem I'm afraid.


    ObjectStorageHelper<T> – A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson

    Monday, September 17, 2012 3:13 PM
    Moderator
  • Correct. [SSISDB].[catalog].[set_execution_parameter_value] must be called before the execution begins. I don't understand why this means you can't use it though - simply get the value from your custom table and pass that value into [SSISDB].[catalog].[set_execution_parameter_value].

    Sorry, I'm not understanding the problem I'm afraid.

    I would like to update my project parameter in the SSIS package itself (when the execution is already started). If I understand it correctly the set_execution_parameter_value needs to be done before executing the package and this can only be done by calling the package via T-SQL?

    Idealy we want to keep all the logic inside the ETL package(s) but we also want to avoid getting this value each time a (child)package is executed.

    I hope my explenation is a bit clear.

    Thanks in advance!

    Bram

    Tuesday, September 18, 2012 7:25 AM
  • Hi Bram,

    Today, SSIS parameters cannot be changed once an execution has begun - they are read-only. Seems like you already know this.

    It sounds as though you want to essentially set a "global variable" (i.e. one that can be seen by all packages in the execution), am I correct? You can't do that today unfortunately.

    Regards
    Jamie


    ObjectStorageHelper<T> – A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson

    • Marked as answer by BramDe Tuesday, September 18, 2012 10:29 AM
    Tuesday, September 18, 2012 9:16 AM
    Moderator
  • Jamie,

    Yes you are correct, I was afraid for this answer, but thx for this confirmation.

    I will have a look for a workaround but I don't like putting the logic outside the packages for maintenance reason.

    THX!!

    Bram

    Tuesday, September 18, 2012 10:29 AM
  • The workaround is in adding a table to which the package writes and reads from. The Execute SQL Task can be used to poll the table values because the returned result (single row) can be mapped to a package variable that you can then consume in your package.

    A comprehensive overview of the technique: http://www.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/64014/


    Arthur My Blog

    Tuesday, September 18, 2012 2:16 PM
    Moderator
  • I have the same need. I would like to be able to udate a parameter with counts from a dataflow. Not sure why I would have to build a table and query it to get the values. Seems like I would be able to just update a parameter.

     My real need is to be able to pass dataflow counts the I am performing in the called SSIS package back to a calling Web Service Application. I figured out how to set the SSIS parameters prior to executing the package, but once the package completes, I want to pass the results back to the calling application. I can populate package variables with those counts, but SSIS will not allow me to update the parameters. Unless I am mistaken, variables cannot be exposed to calling applications. Only parameters.

    Tuesday, November 12, 2013 2:15 PM