EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials SSIS Tutorial SSIS Package Configuration
Secondary Sidebar
SSIS Tutorial
  • SSIS Basic and Advanced
    • SSIS Merge Join
    • SSIS Sequence Container
    • SSIS Foreach Loop
    • SSIS Toolbox
    • SSIS Package Configuration
    • SSIS Variables
    • SSIS Expression
    • SSIS For Loop
    • SSIS Replace
    • SSIS SFTP
    • SSIS Data Types

SSIS Package Configuration

SSIS Package Configuration

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

  • 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,

8

9

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.

10

  • 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

11

  • Choose a new SSIS package in the solution explorer

12

  • Configure the SQL package config database, edit the connection manager, and choose the new OLEDB connection.

SSIS Package Configuration 13

  • Click add and enable package configuration. Add SSIS_CONFIG_DB and configure the connection string property.

SSIS Package Configuration 14

SSIS Package Configuration 15

  • 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 –

  1. CentOS packages
  2. SSIS Interview Questions
  3. Void Linux Packages
  4. Hadoop Configuration
Popular Course in this category
Data Scientist Training (85 Courses, 67+ Projects)
  85 Online Courses |  67 Hands-on Projects |  660+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more