none
Dynamic configuration file name in select configuration type

    Dotaz

  • I have created a package.  I have setup dtsconfig  file under

    1. Package configuration wizard

    2. select configurationtype = xml configuration file

    3. Specify configuration settings directly

    3a. Configuration file name = "C:\a\b\a.dtsConfig" 

    This file consists of servername. When I move this package to QA, i see there we have a different path like "D:\a\a.dtsConfig". Due to this, am getting an error saying path C:\a\b\a.dtsConfig not valid. How do I get / set path dynamically in "Configuration file name" text box? 

    pondělí 9. července 2018 19:13

Všechny reakce

  • You need to use indirect configuration method for this

    i.e store the path of the config file as a separate config item.
    The usual way of doing this is to store the path as an environment variable value in the machine and add a config item in the package of type Environment Variable . You may also choose to store this in a sqlserver table instead

    see

    http://bi-blogger.typepad.com/etlbi_blogger/2008/05/using-indirect-configuration-with-ssis.html

    http://radacad.com/sql-server-indirect-configuration-ssis


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    pondělí 9. července 2018 19:23
  • I cannot use environment variable. We are not allowed to use system variables so I can't use environment variable. Please suggest me otherway of doing.
    pondělí 9. července 2018 19:28
  • I cannot use environment variable. We are not allowed to use system variables so I can't use environment variable. Please suggest me otherway of doing.

    I suggested two ways :)

    The second way is to use SQLserver table to store the file path value as first config


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Upravený Visakh16MVP pondělí 9. července 2018 19:37
    pondělí 9. července 2018 19:35
  • I have two configuration - Configuration1 and configuration2

    configuration1 - it will be xml file. I want to get dynamic path

    Configuration2 - It will be stored in other.

    úterý 10. července 2018 6:18
  • Hi kdinuk,

    1.You can put your xml configuration file into shared path.

    2. Use Dtexec Command to reference alternate configuration file to run the package.

    dtexec Utility

    Regards,

    Pirlo Zhang 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    úterý 10. července 2018 6:42
    Moderátor
  • I have two configuration - Configuration1 and configuration2

    configuration1 - it will be xml file. I want to get dynamic path

    Configuration2 - It will be stored in other.

    Since you cant use environment variable, What you can do is to pass this config file path dynamically using /CONFIG switch or by directly specifying it in your job step properties (assuming you're executing the package from sql agent job)

    i.e through any of the tabs below


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    úterý 10. července 2018 6:48
  • I'm using autosys.

    Here is my command line...

    "D:\MSSQL\130\DTS\Binn\dtexec.exe" /F "K\:\a\b\ETL_a\Packages\a.dtsx" /CONFIGFILE "K\:\mssql\dts\ETL_a\Config\a.dtsConfig"

    At run time, package is still picking "C:\a\b\a.dtsConfig" 

    středa 11. července 2018 12:24
  • I'm using autosys.

    Here is my command line...

    "D:\MSSQL\130\DTS\Binn\dtexec.exe" /F "K\:\a\b\ETL_a\Packages\a.dtsx" /CONFIGFILE "K\:\mssql\dts\ETL_a\Config\a.dtsConfig"

    At run time, package is still picking "C:\a\b\a.dtsConfig" 

    Yeah

    thats true

    It reapplies the design time config values after applying the /CONFIG switch 

    Then only option left for you is what I suggested in the beginning i.e use indirect configuration option using environment variable

    Another alternative is to use project deployment model and use parameters instead of configurations. Then you can even pass values for the parameters directly from dtexec


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    středa 11. července 2018 12:34
  • Hi kdinuk,

    To make the alternate configuration file work at runtime, you need to go to package design and disable package configuration.

    DTEXEC Not Applying Config File at run time

    Regards,

    Pirlo Zhang 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    čtvrtek 12. července 2018 7:55
    Moderátor