XML Configuration Files Effect in SSIS Package

Introduction

When we are creating the package we are using different types of variable or property values with it. This variables or the property value need to be provide proper value before to run the package successfully. A package configuration is a defined property or value that can be modified without updating the package itself.

Package configurations are useful when we want to deploy packages to multiple servers, when we move our packages from a development to production environment.

Case study

We have a destination table object called "Tbl_ProdcutList". This table populated from a flat file. There are two flat file we have called "FlatFile-A.txt" and "FlatFile-B.txt". There is a package level variable called "v_FlatfileChoose". This variable is responsible to load data from flat file. For an example if the value of the variable is "FlatFile-A" then its loaded data from "FlatFile-A" and if the value of the variable is "FlatFile-B" it's loaded data from "FlatFile-B".

IF v_FlatfileChoose == "FlatFile-A" THE Load Data From "FlatFile-A"

IF v_FlatfileChoose == "FlatFile-B" THE Load Data From "FlatFile-B"

How to do that

Step-1 [ Create the Destination File ]

IF OBJECT_ID(N'Tbl_ProdcutList', N'U') IS NOT NULL

   BEGIN

      DROP TABLE Tbl_ProdcutList;

   END

GO

CREATE TABLE Tbl_ProdcutList

       (PRODID          INT         NOT NULL,

        PRODNAME        VARCHAR(50) NOT NULL);

GO

Step-2 [ The Source Flat file ]

Step-3 [ SSIS Control flow tasks ]

Here I am using Execute SQL Task to truncate the Table only.

In the SQL Statement we use

TRUNCATE TABLE Tbl_ProdcutList;

Step-4 [ The Package level Variable ]

Step-5 [ Precedence Constraint Editors Settings ]

Step-6 [ Package Configuration ]

Right click on control flow and the select package configuration from shortcut menu. Click on enable package configuration and click the Add button.

Welcome to Package configuration wizard window came. In the configuration type select the XML configuration file and then close the configuration setting directory and file name. Please look that the extension of configuration file is .dtxConfig.

In the Set property Explorer Window select the variable and click on the value.

Step-7 [ The Configuration XML File ]

Step-8 [ Run the Package with Different Type of Configuration Settings ]

Source:

http://sqlknowledgebank.blogspot.in/2013/06/xml-configuration-files-effect-in-ssis.html