SSIS:Package Configuration

Refer Source Video: SSIS : Package Configuration
Package Configuration
1.)XML Configuration
a.) Direct
b.) Indirect ( Using Environmental Variables)
2.)Environment Variables
3.)SQL Server Configuration
4.)Parent Child Configuration
5.)Registry Entry
------
what is the basic need for package configuration.:SSIS package go through development env. , it has to go through testing phase,uat & then to production.That mean port SSIS to the Production.Go To go to BIDS & change the connection.Microsoft allows to do in more easier way.The "connection string" , variable values," script tasks "to point to right database -

1.Right click on Package workspace Area - Choose "Variable" -  2.SQL query analyzer:

Select * from Production..TransactionHistory where ProductID=799 and TransactionDate<>'2007-09-01 00:00:00:000'

3.The Package when runs just shows Count of Orders for a "Product" for a particular "date".

Environment Variables:Generally not used. 
-------------------------------
My Computer->Properties->system->Advances->Environmental Variables

a.Create an "Environmental Variable" : ProductId  Value:799
b.Close the open session of "BIDS" , reopen the .sln (solution) In the "Package" window, right click anywhere choose "Package Configuration" , choose check box "Enable package configurations".

It opens a wizard , add a new package configuration.
Configuration type:Environment Variable
Load all Environment Variables: choose :"ProductId" , then Map it to,
"ProductId" Filter
Package
Variables
...
ProductId_Filter
 - properties

Now the value shows "799" , now after running the package it will take the Environment Variable value.At the runtime it does not take the "Package Level Variable" value but the "Environment Variable" value.

Registry Entry: Not at all used.
-------------------
1.start-run-regedit-
HKEY_CURRENT_USER- add a key-say "SSIS",right click on it,New,String Value-call "Value"
Value Data - '2007-09-01 00:00:00 000'
right click anywhere choose "Package Configuration" , 
It opens a wizard , add a new package configuration.
Configuration type:Registry Entry
Registry Entry:SSIS

It should map to the DateTransaction_Filter - Value
If you change any value in Environment variable, need to reload the Environment variable again.
--------------------
Through Environment variable : Dynamically changing variable values at run time.
----------------------
XML Configuration:That is generally used.
--------------------------
a.)Direct b.)Indirect
a.)Direct :right click anywhere choose "Package Configuration" 
Configuration type:XML configuration file
-Specify configuration settings directly
Configuration file name:c:\temp\ abc.dtsconfig
       Connection Manager-Database Connection
Map:ConnectionString of  Database(not variable)

This shows the abc.dtsconfig file.Open it to see its XML file defining the connection.
In this file change the Catalog: name which is the Database Name , then running
the SQL will show records found from that database.
Right Click on Databaseconnection: can see the Database name whats in the XML
file abc.dtsconfig
b.)Indirect:Copy the path of the abc.dtsconfig  from Configuration file name:c:\temp\ abc.dtsconfig
Create an environment variable:ConfigPath and it will have info. where this abc.dtsconfig
file is which is holding connection information to database.

As the New Env. Variable is added, need to restart the package.

right click anywhere choose "Package Configuration"  - when tried to edit it gave error,

so removed that and created another and 

Configuration location is stored in environment variable:ConfigPath
--------------------------
3.)SQL Server Configuration

Remove the "Environment Variabale" , "Registry Entry" Configuration from "Package Configuration" .

Only remaining the Configuration type:XML configuration file (b.)Indirect , which has
the connection information in an Environment Variable that points to the path
"abc.dtsconfig".

right click anywhere choose "Package Configuration" 
Configuration type:SQL Server
New ->Database->specify "Server name" "DataBase":

You will see a New Table.In the next window.

CREATE TABLE [dbo].[SSIS Configuration]
(
ConfigurationFilter   NVARCHAR[255] NOT NULL,
ConfigurationValue   NVARCHAR[255]  NULL,
PackagePath     NVARCHAR[255] NOT NULL,
ConfigurationValueType NVARCHAR[255] NOT NULL
)

Remove the gap between the tablename

CREATE TABLE [dbo].[SSISConfiguration]
(
ConfigurationFilter   NVARCHAR[255] NOT NULL,
ConfigurationValue   NVARCHAR[255]  NULL,
PackagePath     NVARCHAR[255] NOT NULL,
ConfigurationValueType NVARCHAR[255] NOT NULL
)

This will create a table and this one holds all values that is specified in the Environment
variables. So for moving the configuration details from "Dev" to "Test" or "Production"

The script generation sql of this table and the Environment Variable details to be given
to the person who is moving it.

Configuration filter:VariableDemo { in the screen : Configuration type:SQL Server }

Map: ProductID,TransactionDate { Now as the Environment Variable value deleted , it 
        takes the variable values).
--------------------------
Briefnote:In the "Package Configuration" , specifying "connection" and Mapping "Variables" package level, to take value or it takes from environment variable.
The connection parameter details file path can be in a environment variable.
In 'SQL Server Type" : SSISConfiguration table holds all variable values.
--------------------------
Query analyzer: Select * from SSISConfiguration

Configuration filter:VariableDemo  thats defined above uniquely tells this package filter
and PackagePath holding the variable values name.
--------------------------
SSIS Package i.e dtsx file open in notepad , you will see its a XML file.
--------------------------
UPDATE  SSISConfiguration SET  ConfigurationValue= WHERE  PackagePath=
AND ConfigurationFilter=
--------------------------
ConnectionManager:
DatabaseConnection
DEV.TestDB -> the new value in the table "SSISConfiguration" and runs the package.
--------------------------
For Migration:Actually the values in Environment Variables.

Choose DB:rightclick->Task->Generate Script
Choose Table 'SSISConfiguration" , 
Table/View:Option choose "Script Data" -> True
Then choose Script to Query Window.

It will show Create statement for  SSISConfiguration table and even the Insert statement
for the variables.The Deployment person will run in that environment say Production.

---
Guideline:Just only the 'Environment Variable" where things need to change only.

So the  3.)SQL Server Configuration XML configuration file (b.)Indirect , which has
the connection information in an Environment Variable that points to the path
"abc.dtsconfig".
----