Updated March 15, 2023
Definition of SSIS Package Configuration
The package configurations enable the properties of variables and run-time in the development environment. It is used to build flexible packages which are easy to distribute and deploy. Microsoft Integration provides multiple configuration types like Environment variable, registry entry, XML configuration file, SQL server table, and Parent package variable. So all the services are offered to build and configure a package according to the user preference. In this article, the SSIS package configuration is briefly discussed.
SSIS Package configuration overview
The package configuration is the best method to save the configurable parameters used in the package. The parameters help the user to read the expressions, variables, and WMI tasks which are different methods to save the configurable values fed to the packages at runtime. It is an effective way to save the values in other locations where a few types of variables cannot be saved, like server tables, XML files, and registry values. The package configuration is used to edit the package at runtime.
The package configuration gives maximum flexibility to execute the SSIS package, allowing configuring the packages in various environments instead of modifying or editing the file in packaging. When the package is compiled, not all the parameters are known, like variable values and file location. The user doesn’t want to push the values in the package by giving the runtime info. So when the package is executed, the values are saved in the appropriate configuration store and used at the execution of packages. The SSIS supports the configuration capabilities in five different data stores registry entry, the environment variable, XML file configuration, Parent package variable, and SQL server.
- XML file configuration: The package information is stored in the XML file on the file system. The configuration provider allows the user to save different configuration settings in a single file. Instead of hardcoding the file, the path can be saved in the user-defined variable. So by using this option, the user can edit the XML file and delicate the settings along with packages.
- Environment variable: Here, it saves the configuration value in an environment variable. It is used to save single parameters in configuration. By giving the environment variable, it is placed on every machine where the package is used to run and checks that the package configuration is valid. It is also used to set the environment variable, which is done at the initial phase of the package environment.
- Registry entry: It enables the user to save the configuration values in registry values. Here the single values are given, and it has an option to mention the environment variable, which comprises a registry key. The registry has configuration entries which are a reliable way to save and secure the configuration values.
- Parent package variable: It enables the user to give a complete qualified variable in various packages, which is available as a source of configuration values. Only a unit value can be saved in the configuration store. It is a reasonable method to associate the packages and transfer the values between them at the execution time. When any one of the packages is based on the result of the other package, then the preferred option is perfect.
- SQL Server: The SQL server enables the user to create a configuration table in SSIS that the user can specify. As this table can hold different configuration values, the filter is preferred to allow the system to return the suitable configuration values. It enables the user to give multiple configuration values saved in the specified filter name. It is the preference of the user to give the database table and an additional filter in the environmental variable.
Using SSIS package configuration:
When deploying the package in a production environment, editing the package configuration in BIDS is not advisable. Instead, the user can configure the settings, values, and variables of the connection manager in the configuration settings of SSIS. The list of available package configuration in SSIS are described below,
The user has to right-click on the control flow region to open the context box. Because the configuration setting is not available in the package configuration for package designers. The option of SSIS is used to navigate the project menu and choose convert to package deployment model in the sub-menu.
- Once the menu is selected, the new pop-up appears as a confirmation box. Then choose ok. Once the OK button is selected, the SSIS will change the current package to the effective deployment model. Choose OK. Then conversion window is closed.
- Individual package configuration is explained in SSIS, and this duplication table is displayed in the SQL server.
- The simple package is comprised of executing SQL task rowset to run the package configuration. The OLEDB connection is used to customize the SQL command.
- Assignment of variable is made in mapping tab parameters.
- The prior elements in the tables are returned by SQL statement
- Run the package configuration to look at the output.
Create SSIS package configuration
To create a new integration service, follow the below steps.
Add a new SSIS package, then add the connection managers in the SSIS config and the environment variable to assign the property of the connection string. Finally, add a server package in SQL to assign the connection string.
- Create a new file inside the new project
- Choose a new SSIS package in the solution explorer
- Configure the SQL package config database, edit the connection manager, and choose the new OLEDB connection.
- Click add and enable package configuration. Add SSIS_CONFIG_DB and configure the connection string property.
- Add SQL server configuration package and give a configuration name. Then click finish.
- Review the package configuration and give the configuration values, configuration filter, package path, and value type configuration.
Conclusion
Hence these are the few steps in package configuration in SSIS and can be modified according to the user’s preference. In addition, the files can be saved and altered to use the package configuration effectively.
Recommended Articles
This is a guide to SSIS Package Configuration. Here we discuss the definition, overview, package configuration, and Create SSIS package configuration with code implementation. You may also have a look at the following articles to learn more –
