Post reply

SSIS - Change Configuration File Value

  • March 9, 2012 at 12:50 am

    #258375

    Hi there

    I have a SSIS package that needs to run every weekday and exports data from the previous run.

    So I have two variables, lastExport and todaysDate, so basically what happens is the export runs off lastExport, the filename gets todaysDate and then at the end of the package, lastExport is set to todaysDate.

    I have setup a configuration file to keep these values while its running with the Agent, but I need to change the value of lastExport in the configuration file at the end of the package. How do I go about this? Can I just create a Script task and edit the XML directly or is there a better way?

    Thank you

  • March 9, 2012 at 2:11 am

    #1457017

    Theoretically, you can, but I'd say this is a bit of a misuse of what configuration files are for.

    They're generally to control the environment dependent values of a package, so they can be moved to a different environment, passwords be changed etc., not to control it's current run state.

    This type of variable that needs to store the history of package execution is generally much better implemented in a database table that stores the run state and then you query out to a variable at runtime using an execute SQL task. E.g. you could store all of your export dates along with a created timestamp in a table and pick out the latest one at runtime. That way you have a complete history as well.

  • March 9, 2012 at 2:45 am

    #1457024

    Thanks Howard, when I posted the OP, I went and created a package_variable table, with the ssis task, variable and value and using that to populate and update the variables.

    works great.

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

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