locked
change configuration filter at runtime RRS feed

  • Question

  • I need to change my configuration filter at runtime. is it possible with scriptask on preexecute.

    any other idea is welcome.  thanks


    http://joeydj.com/blogs

    Thursday, March 7, 2013 11:43 AM

Answers

  • We can use the /Set option to change the location from which package configurations are loaded. However, we cannot use the /Set option to override a value that was specified by a configuration at design time. For example, we can use following code to apply configuration file to package:

    /ConfigFile C:\SSISConfiguration.dtsConfig

    The way in which the dtexec utility applies configurations affects the following command-line options:
    • Use the /Connection or /Set option at run time to load package configurations from a location other than the location that you specified at design time.
    • Use the /ConfigFile option to load additional configurations that you did not specify at design time.

    However, these command-line options do have some restrictions:
    • Cannot use the /Set or the /Connection option to override single values that are also set by a configuration.
    • Cannot use the /ConfigFile option to load configurations that replace the configurations that you specified at design time

    For more details about how package configurations are applied, see:
    SSIS Package Configurations: http://msdn.microsoft.com/en-us/library/ms141682.aspx
    Behavior Changes to Integration Services Features in SQL Server 2008 R2: http://msdn.microsoft.com/en-us/library/bb500430.aspx

    Please feel free to ask if you have any question.

    Thanks,

    Zaim Raza.

    • Marked as answer by JoeyDj Thursday, March 7, 2013 1:50 PM
    Thursday, March 7, 2013 1:11 PM

All replies

  • You mean this one?

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

    Thursday, March 7, 2013 12:08 PM
  • yup but i need to do it at runtime


    http://joeydj.com/blogs

    Thursday, March 7, 2013 12:34 PM
  • Hi,

    I hope following links help you, which use the SSIS object model to set the configuration filter.

    http://www.rdacorp.com/2010/02/ssis-table-driven-package-configurations-with-row-level-filtering/

    and also try to use the dtexecutil with /SET Clause.

    Thanks,

    Zaim Raza.

    Thursday, March 7, 2013 12:52 PM
  • thanks im using this

    dtexec /f "c:demo\demo.dtsx" set \package.configurations[Configuration1].properties[ConfigurationFilter].value;int-002


    http://joeydj.com/blogs

    Thursday, March 7, 2013 1:01 PM
  • is it case sensitive it says it cannot find the path


    http://joeydj.com/blogs

    Thursday, March 7, 2013 1:01 PM
  • Myerror log..............

    Microsoft (R) SQL Server Execute Package Utility
    Version 10.50.1600.1 for 32-bit
    Copyright (C) Microsoft Corporation 2010. All rights reserved.

    Started:  9:02:33 PM
    Warning: 2013-03-07 21:02:33.55
       Code: 0x8001F02F
       Source: table_to_table2
       Description: Cannot resolve a package path to an object in the package ".configurations[Configuration1].properties[ConfigurationFilter].value". Verify that the package path is valid.
    End Warning
    Warning: 2013-03-07 21:02:33.55
       Code: 0x80012017
       Source: table_to_table2
       Description: The package path referenced an object that cannot be found: "\package.configurations[Configuration1].properties[ConfigurationFilter].value". This occurs when an attempt is made to resolve a package path to an object that cannot be found.
    End Warning
    DTExec: Could not set \package.configurations[Configuration1].properties[ConfigurationFilter].value value to int-002.
    Started:  9:02:33 PM
    Finished: 9:02:33 PM
    Elapsed:  0.249 seconds


    http://joeydj.com/blogs

    Thursday, March 7, 2013 1:03 PM
  • We can use the /Set option to change the location from which package configurations are loaded. However, we cannot use the /Set option to override a value that was specified by a configuration at design time. For example, we can use following code to apply configuration file to package:

    /ConfigFile C:\SSISConfiguration.dtsConfig

    The way in which the dtexec utility applies configurations affects the following command-line options:
    • Use the /Connection or /Set option at run time to load package configurations from a location other than the location that you specified at design time.
    • Use the /ConfigFile option to load additional configurations that you did not specify at design time.

    However, these command-line options do have some restrictions:
    • Cannot use the /Set or the /Connection option to override single values that are also set by a configuration.
    • Cannot use the /ConfigFile option to load configurations that replace the configurations that you specified at design time

    For more details about how package configurations are applied, see:
    SSIS Package Configurations: http://msdn.microsoft.com/en-us/library/ms141682.aspx
    Behavior Changes to Integration Services Features in SQL Server 2008 R2: http://msdn.microsoft.com/en-us/library/bb500430.aspx

    Please feel free to ask if you have any question.

    Thanks,

    Zaim Raza.

    • Marked as answer by JoeyDj Thursday, March 7, 2013 1:50 PM
    Thursday, March 7, 2013 1:11 PM
  • Hi,

    Obviously it has been awhile, but I solved this using a different approach that may work for some of you.

    Rather than attempting to actually reset the configuration filter I created at design time, I instead buffer the package execution with a job step before and after execute to change the filter in the configuration table to be the filter I set in design time. 

    To explain:  At design time I set my configuration filter value to "UseMe."  Then, upon deployment of the package, I created an sql job step executed just before the execute package step which reads "UPDATE dbo.[SSIS Configurations] SET ConfigurationFilter = 'UseMe' WHERE ConfigurationFilter IN ("ConfigurationFiltersIWantToApply")".  Then, after the package execution step has completed, I execute another job step which returns the SSIS Configurations table back to its original state.  Alternatively, you could create a separate table, copy in the desired configurations, and then drop the table after the package runs, if you want to avoid touching the original configuration table values (might be less typing/chance for error especially if you are using multiple ConfigurationFilter values or other packages may try to access the SSIS Configurations table at the same time).  I haven't done it yet, but I suppose ideally you would encapsulate the create separate table solution in a stored procedure and just type EXEC storedProcedureName 'DelimiterSeparatedConfigurationFilterValues'

    I tried putting this logic in a pre-execute task as you originally desired, but pre-execute (and all of the other events, so far as I can tell from my experimentation) is still post-configuration, so I had to go with the separate job steps solution.

    If you are executing packages from a master package rather than as individual job steps (like looping through executing the same package and providing a different value as an input parameter), I think you will need to write a script to configure the values yourself, as described in the rdacorp article.  That's a pretty neat, short, and flexible solution that you could build in to your ssis template and use in place of or alongside of the built-in Microsoft configuration solution.

    UPDATE:  I did some more thinking on this today, and actually the rdacorp blog post's method is a little bit limited in that you have to know ahead of time what you are configuring, unless possibly you can write some dynamic C Sharp code, etc., which I didn't feel like attempting to shoehorn into the SSIS script task today.  That sharply limits its reusability and dynamism.  You can, however, execute a stored procedure, which I previously described as a job step prior to package execution, inside of the for loop of a master package before the child package instead.  That just another way to get you ahead of the package configuration time. I suppose you could even create a dummy master package which didn't do anything but set the configuration before starting the child package.  That solves any need I can imagine for setting configuration filters at runtime.

    More details at my just-now created blog's first post:  http://davidsbiblog.wordpress.com/2014/07/02/ssis-dynamic-package-configuration/


    Wednesday, July 2, 2014 2:33 PM