SSIS 2012: How do I change the project's environment variables?

Question

I want to change 2 date project variables, StartDateTime and EndDateTime. I have another variable called RunType. Very simply, I want to read the value RunType first. If it is set to "Incremental" I want to change the StartDate from whatever it is to 12:00:00 AM yesterday and the EndDate to 11:59:59 PM yesterday. The errors I get from trying to write back the values seem to indicate writes are out on project level variables. Is this true--or is there something I need to do differently when dealing with these project level variables? I thought of creating package level variables, a control table, blah blah... It seems like overkill for this.

When I test the package manually by changing the parameter values under the integration service catalog/environments--I get the range I expect. This package will run via sql agent job. Is there a pre-ssis step I can create and execute to do this simple task outside of ssis?

You seem to be mixing up variables and parameters. You don't have project level variables, the highest level you can define them on are packages. Parameters cannot be modified at run time. — JodyT, Jun 13 '13 at 22:56
Now that I look at the SSIS project itself, I see they are called parameters--why are they called variables under environments in the integration services catalogs? — plditallo, Jun 13 '13 at 23:09
@JodyT -- Thanks for clarifying. Now that you identified them as parameters, I found where they exist under ssisdb in [internal].[object_parameters] by object_name -- there under parameter_name are the range dates I am interested in. — plditallo, Jun 13 '13 at 23:22
You have 3 project variables, StartDateTime, EndDateTime and RunType and you wish to change the values of the first 2 based on a value of RunType? — billinkc, Jun 14 '13 at 02:10

score 12 · Accepted Answer · answered Jun 14 '13 at 02:19

While the values may be stored in internal.object_parameters, resist the temptation to edit the values in those tables directly. Instead use the supplied methods for manipulation. In this case, the stored procedure catalog.set_environment_variable_value.

Below is a sample of how you can programmatically change the value of an environment variable. I would see this as being a TSQL jobstep in SQL Agent job that runs prior to your package launching to ensure the correct values for StartDateTime and EndDateTime are set as expected.

DECLARE @var sql_variant;

DECLARE
    @StartDateTime date
,   @EndDateTime datetime
,   @RunType bit = 1;

SELECT
    @StartDateTime = CAST(dateadd(d, -1, CURRENT_TIMESTAMP) AS date)
,   @EndDateTime = DATEADD(s, -1, cast(cast(current_timestamp AS date) AS datetime))

SELECT @StartDateTime, @EndDateTime;


IF (@RunType = 1)
BEGIN
    SET @var = @StartDateTime;
    EXECUTE [SSISDB].[catalog].[set_environment_variable_value] 
        @variable_name=N'StartDateTime'
    ,   @environment_name=N'MyEnvironmentName'
    ,   @folder_name=N'MyFolder'
    ,   @value=@var;

    SET @var = @EndDateTime;
    EXECUTE [SSISDB].[catalog].[set_environment_variable_value] 
        @variable_name=N'EndDateTime'
    ,   @environment_name=N'MyEnvironmentName'
    ,   @folder_name=N'MyFolder'
    ,   @value=@var;
END
ELSE
BEGIN
    PRINT 'Logic goes here to handle the other conditions for RunType'
END

you are so right... the temptation was almost overwhelming! :) — plditallo, Jun 19 '13 at 19:24

SSIS 2012: How do I change the project's environment variables?

1 Answers1