• Nikos Miliotis (6/9/2009)


    In which database, do i have to store the SSIS_Config table?

    SQL configuration should have its own connection manager, so all packages (in all environments) get configuration settings from the same table (subject to network and/or security constraints). It doesn't have to be in the same database or even the same server as the package storage location or any of the data flow connections. The SQL configuration connection manager can itself be configured (or indirectly configured), so you can have multiple multi-dimensional configuration sets. You can also have more than one configuration connection manager in a package to add more flexibility (and confusion). Basically, the configuration table (including the views and procedures) can be just about anywhere.

    For example, a package might be stored in msdb on server A (or in the filesystem of server B), execute on computer C, get configuration from a table on server D, and operate on data on servers E, F, or G depending on environment. The same package (either loaded from the same location or deployed by a simple copy with no modification) might also execute on computer H and be indirectly configured to get configuration from a table on server J and operation on data on servers K, L, or M.

    A major consideration is what is the most reliable location. If your configuration server is not available, packages will fail. In my environment there is one central SQL Sever that HAS to be available, so this is not a problem. Packages running on other servers will have connections to the main server and will fail anyway if it is down.