You design a Business Intelligence (BI) solution by using SQL Server 2008. You develop 10 SQL Server 2008 Integration Services (SSIS) packages. You plan to include package configuration for all the packages.
The package configuration has the following requirements:
-All configurations are stored in a single location.
-Configuration variables are easily backed up and restored.
-Indirect configuration is used.
-The database administrators will use SQL Server client tools to change the configuration values.
You need to create package configurations for the packages to meet the configuration requirements.
What should you do?
A.
Store all configuration information in a SQL Server table.
Specify configuration database connectivity settings in an environment variable.
B.
Store all configuration information in a SQL Server table.
Specify configuration database connectivity settings in the SQL Server table.
C.
Use XML configuration files for all packages.
Store each XML configuration file in a common folder.
Specify the XML configuration file location in an environment variable.
D.
Use XML configuration files for all packages.
Store each XML configuration file in a common folder.
Specify the XML configuration file location in the configuration settings.
Explanation:
Tip: "SQL Server client tools" = "SQL Server table / environment variable"SQL Server table configuration Type
A table in a SQL Server database contains the configuration. The table can include multiple configurations.
If you select the SQL Server configuration type, you specify the connection to the SQL Server database in which you want to store the configurations. You can save the configurations to an existing table or create a new table in the specified database.
Direct and Indirect Configurations
Integration Services provides direct and indirect configurations. If you specify configurations directly, Integration Services creates a direct link between the configuration item and the package object property. Direct configurations are a better choice when the location of the source does not change. For example, if you are sure that all deployments in the package use the same file path, you can specify an XML configuration file.
Indirect configurations use environment variables. Instead of specifying the configuration setting directly, the configuration points to an environment variable, which in turn contains the configuration value. Using indirect configurations is a better choice when the location of the configuration can change for each deployment of a package.