Using Set Values and Configurations on a sql agent ssis job

Posted on 2011-10-24
Last Modified: 2012-05-12
Can you run an SSIS package from a SQL Agent job setting the packages variables by both an xml configuration file as well as setting a certain variable using Set Values by entering something like:


in property path and the value I want to change to in the Values column?

I basically want my package that has 20 variables listed in the configuration file to run with all the values in that xml file aside from a datetime variable which I want to run with today's date rather than the value in the config file.

Thought I may be able to do it this hoping that Set Values would take precedence over the configuration file.
Question by:Grffster
    LVL 37

    Expert Comment

    If you specify both the XML file and the variable assignment through the job, then I'm not sure which one takes precedence.  They will both be accepted, but if values for the same variable are specified through both XML and additional job config, then only one of them will be applicable.
    However, if you use Package Configurations to specify the XML file and set the additional variable through the job, then it should work as expected.

    Another option that I see, in case you do want to specify both XML file and variable through the job, is to ensure that the variables for which values are specified through the job, are not mentioned in the XML file.
    But maybe that's already what you were trying to do?

    More info:

    Author Comment

    I don't entirely understand what you mean by:

    However, if you use Package Configurations to specify the XML file and set the additional variable through the job, then it should work as expected.

    Isn't this what you're describing in your final paragraph? In other words, leave End Date datetime variable out of the xml configuration file so the project runs that, but set it to be today's date in the set values area on the job? i.e. make them mutually exclusive.
    LVL 37

    Accepted Solution

    What you describe is indeed what I meant.

    What I meant with Package Configurations is that you specify the XML file through configuration in the package itself.  This is done by right-clicking the yellow background in the Control Flow of the package.

    But it doesn't really make a difference, you can also specify the XML file in the Configurations tab in the agent job.  Both ways will work.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now