Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Python Programs
    • Java Programs

SSIS Package Configuration

by suresh

While deploying the packages to the production environment, it is a bad practice to edit the package configuration settings in BIDS. If you place connection manager details inside the package, then you have to edit them using BIDS only. To avoid these circumstances, we had SSIS Package Configuration settings. You can use them to save the connection manager details, variables, values, etc. Here, we show you the list of available SSIS Package Configurations and its description.

By default, the SSIS Package Configuration setting is not available in package designers. For instance, if you right-click on the control flow region, it will open the context menu. From the list, you can see there is no Package Configuration.. option.

SSIS Package Configuration 10

To get that SSIS Package Configuration option, Please navigate yourself to Project Menu and select Convert to Package Deployment Model sub-menu.

SSIS Package Configuration 11

Once you select that menu, a new pop-up window will display, as we showed below. Click the OK button

SSIS Package Configuration 12

After you click the OK button, SSIS will convert the current package into the deployment model. Click OK to close the conversion window.

SSIS Package Configuration 13

Now, if you right-click on the control flow region, the context menu will display the Package Configurations.. option. You can select this option to add the package configurations.

SSIS Package Configuration 14

Following are the list of Configuration types available in SQL Server Integration Services:

  • XML Configuration File: This type will save the configuration settings in an XML file. It is the most common approach while deploying the report. Please refer to SSIS Package Configuration using XML Configuration File, and Part 2 to understand the XML Configurations.
  • Environment Variable: It allows you to use the Environment variables to save the configuration settings, or package variables values. Please refer to SSIS Package Configuration Using Environment Variable article to understand the settings.
  • Registry Entry: You can also save the configuration settings in Registry. Visit SSIS Package Configuration using Registry Entry to know the registry settings.
  • Parent Package Variable: This type will save the configuration settings in a variable. You can use this variable to update properties in child packages. Visit the Parent Child Package
  • SQL Server: Configuration settings saved in a table present in the SQL Server. Please see SSIS Package Configuration using SQL Server to know the SQL settings.
SSIS Package Configuration 15

Sample package for SSIS Package Configuration

We are going to use this package in the series of articles that explains each and individual package configuration type in SSIS. Before we get into the SSIS Package Configuration demo, let me show you the table that we are going to use. It is the Duplicate Employee Table in SQL Server.

SSIS Package Configuration 1

As you can see from the below screenshot, it is a simple package that contains one Execute SQL Task and Script Task. Please refer Execute SQL Task in SSIS Single Rowset article to learn the package configurations.

SSIS Package Configuration 2

Let me show you the configuration settings inside the Execute SQL Task. Here, we selected the Connection Type as OLE DB Connection, which is connecting to the SQL Tutorial database. Next, we are using the Direct Input as the SQL statement and write the custom SQL command. Lastly, we changed the ResultSet from NONE (default) to Single row.

SSIS Package Configuration 3

Let me click the … button to show the custom SQL Query.

-- SSIS Package Configuration Example
SELECT [FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[Sales]
      ,[HireDate]
      ,[YearlyIncome]
  FROM [DuplicateEmployee]
  WHERE [YearlyIncome] = ? AND
        [Education] = ?
SSIS Package Configuration 4

Under the Parameter Mapping tab, we are assigning the variables to the parameters. It means first question marks beside the Yearly Income assigned to Input Yearly Income variable, and the other question mark to Input education variable.

SSIS Package Configuration 5

As you can see from the below screenshot, we are assigning the previously created variables to all the columns that are returned by the SQL statement.

SSIS Package Configuration 6

I think I forgot to show the existing variables. Let me close the Execute SQL Task editor, and right-click on the design to select the variables.. option. From the below screenshot you can see the default values of InputEducation= Masters Degree, and InputYearlyIncome = 90000

SSIS Package Configuration 7

C# code inside the Script Task is as shown below.

SSIS Package Configuration 8

Let us Run the SSIS Package Configuration package to see the result.

SSIS Package Configuration 9

Placed Under: SSIS

  • What is SSIS
  • Install SQL Server Data Tools
  • Learn SSIS in 28 Days
  • Create a SSIS Project
  • Create SSIS Package
  • SSIS Connection Manager
  • OLE DB Connection Manager
  • SSIS ADO Connection Manager
  • ADO.NET Connection Manager
  • SSIS Cache Connection Manager
  • SSIS Excel Connection Manager
  • SSIS File Connection Manager
  • SSIS FTP Connection Manager
  • SSIS SMO Connection Manager
  • Source Assistance in SSIS
  • Flat File Source in SSIS
  • OLE DB Source in SSIS
  • Excel Source in SSIS
  • ADO.NET Source in SSIS
  • SSIS FLAT FILE Destination
  • SSIS OLE DB Destination
  • SSIS ADO.NET Destination
  • SSIS Transformations
  • SSIS Audit Transformation
  • SSIS Aggregate Transformation
  • SSIS Aggregate Advanced Mode
  • SSIS Cache Transformation
  • SSIS Character Map
  • SSIS Conditional Split
  • SSIS Copy Column
  • SSIS Data Conversion
  • SSIS Derived Column
  • SSIS Export Column
  • SSIS Fuzzy Grouping
  • SSIS Fuzzy Lookup
  • SSIS Import Column
  • SSIS Lookup Introduction
  • Lookup – OLE DB Connection
  • SSIS Lookup in Full Cache Mode
  • SSIS Lookup – Case Sensitivity
  • SSIS Merge Transformation
  • SSIS Merge Join Transformation
  • SSIS Merge Join – Left Outer Join
  • SSIS Merge – Right Outer Join
  • SSIS Merge Join – Full Outer Join
  • SSIS Multicast Transformation
  • SSIS OLEDB Command
  • OLEDB Command – Delete Data
  • OLEDB Command-Update Data
  • SSIS Percentage Sampling
  • SSIS Pivot Transformation 2008
  • SSIS Pivot Transformation
  • SSIS Row Count Transformation
  • SSIS Row Sampling
  • Script Component as Source
  • Script Component as Destination
  • SSIS Script as Transformation
  • SSIS Sort Transformation
  • SSIS SCD Type 0
  • SSIS SCD Type 1
  • SSIS SCD Type 2
  • SSIS Term Lookup
  • SSIS Term Extraction Intro
  • Term Extraction – Extract Nouns
  • SSIS Extract Noun Phrases
  • Extract Nouns & Noun Phrases
  • Term Extraction – Exclusion Tab
  • SSIS Unpivot Transformation
  • SSIS Union All Transformation
  • SSIS For Loop Container
  • SSIS ForEach File Enumerator
  • SSIS ForEach SMO Enumerator
  • SSIS ForEach Variable
  • SSIS Foreach NodeList
  • Foreach ADO.NET Schema Rowset Enumerator
  • SSIS Bulk Insert Task
  • SSIS Data Profiling Task
  • Execute T-SQL Statement Task
  • SSIS Execute SQL Task Intro
  • SSIS Execute SQL Task Example
  • Execute SQL Task- Single Rowset
  • Execute SQL Task – Full Row Set
  • SSIS Execute Package Task
  • Execute Packages in SQL Server
  • Execute Packages in File System
  • SSIS Execute Package Project Reference
  • SSIS File System Task
  • SSIS File System Task- Copy Files
  • File System Task-Copy Directory
  • File System Task – Delete Files
  • File System Task – Delete Folder
  • File System Task -Move Directory
  • SSIS File System Task -Move File
  • File System – Move Multiple files
  • File System Task – Rename File
  • File System Task – Set Attributes
  • SSIS FTP TASK
  • SSIS Create Local Directory
  • SSIS Create Remote Directory
  • SSIS FTP Task Send Files
  • SSIS FTP – Send Multiple Files
  • SSIS FTP Task Delete Local Files
  • FTP TASK Delete Local Directory
  • FTP Task Delete Remote files
  • SSIS Delete Remote Directory
  • SSIS FTP Task Receive Files
  • SSIS FTP Receive Multiple Files
  • SSIS Script Task
  • Transfer SQL Server Objects Task
  • Transfer SQL Table Structures
  • Transfer SQL Tables with Data
  • Transfer SQL Stored Procedures
  • Transfer User Defined Functions
  • Transfer SQL Views in SSIS
  • SSIS Web Service Task
  • SSIS XML Task-Validate XML File
  • Transform XML File using XSLT
  • XML Task-XML files Differences
  • Create SSIS Catalog
  • Package Deployment using BIDS
  • Deploy Package Using SQL
  • Deploy using SQL Server Wizard
  • SSIS Breakpoints
  • SSIS Checkpoints
  • SSIS Error Handling
  • SSIS Event Handlers
  • SSIS Transactions
  • SSIS Logging
  • SSIS Parameters
  • SSIS Package Configuration
  • Configure using SQL Server
  • Config using Registry Entry
  • Conf with Environment Variable
  • SSIS XML Configuration File
  • XML Configuration File Part 2
  • SSIS Package Protection Level
  • SSIS Incremental Load
  • Incremental Load Example 2
  • SSIS Remove Double Quotes
  • C Tutorial
  • C# Tutorial
  • Java Tutorial
  • JavaScript Tutorial
  • Python Tutorial
  • MySQL Tutorial
  • SQL Server Tutorial
  • R Tutorial
  • Power BI Tutorial
  • Tableau Tutorial
  • SSIS Tutorial
  • SSRS Tutorial
  • Informatica Tutorial
  • Talend Tutorial
  • C Programs
  • C++ Programs
  • Java Programs
  • Python Programs
  • MDX Tutorial
  • SSAS Tutorial
  • QlikView Tutorial

Copyright © 2021 | Tutorial Gateway· All Rights Reserved by Suresh

Home | About Us | Contact Us | Privacy Policy