.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Best way to manage SQL Server Package Configuration data

Posted By:      Posted Date: May 22, 2011    Points: 0   Category :
I inherited approximately 150 SSIS packages doing various ETL operations. They use a custom table that requires that we add a new column when a package requires a new type of config data storage.
As part of an upgrade from 2005 to 2008 and a partial rewrite effort I am exploring, among other changes, switching from storing config data in the flat/wide table I mentioned to using SQL Server Package Configurations to manage configuration data. What do you think of the following?
  1. Having two Package Configurations per SSIS package. Both will be pointed to the same database containing only the config table, but with different filters. One filter will be specific to the package that will contain package-specific configuration data and one will be called "global" which will contain configuration data that all packages share (things like "default alert email" should Armageddon ensue during package execution).
  2. How best to manage configuration data when moving a brand new package from DEV to STAGING to PRODUCTION?
  3. How best to manage properties for the connection object used to initially connect to the Package Configuration database/table?


Make everything as simple as possible, but not simpler.
-Albert Einstein

View Complete Post

More Related Resource Links

SSIS Package for Delta Data between Oracle and SQL Server


Hi all

I have BI Server and Tables and Data Populated using SSIS package.Now Data is ready in BI server.Next week Ihave to same excersize to filll the Data(DROP tables,CREATE table and Pump the Data)

Can any body give an idea how to create SSIS package which can pupm the Delta Data Oracle and SQL Server.


Thanks in advance




Creating a SSIS package to get data from free DBF (Visual FoxPro table) in 64 bit SQL Server


Hi everybody,

I originally posted in the wrong forum MSDN Thread

I'm trying to create (for test purposes) a package to load data from VFP table into SQL Server 64 bit. So far I can not figure out how to do it - I created DSN as outlined in Todd's blog, but I don't know what to do next.

Can someone please give me step by step instructions so I will know if it's possible or not? I was under impression it's not possible, but Todd's blog seems to suggest otherwise.

Thanks in advance.

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

SSIS Package retains designer setting, not Using Configuration From SQL Server Table.


Hello all,

I am using package configurations to set SSIS package variables from the SQL server Database [SSIS Configurations] table.   I have an environment variable set as my first configuration, to hold my database connection, and then the subsequent configurations are SQL configuration entries and held in the [SSIS Configurations] table.

When testing in BIDs, the connection is OK and the designer shows the correct values have been picked up.  All good!

But, when we change the database value in the [SSIS Configurations] table, the old configuration is still being used in BIDs.

I have checked using Profiler that BIDS is calling and retrieving the correct value from the table but the modified value is not being used in BIDs, instead it is retaining the old value when the package was designed.

How do I force the SSIS package to use the configuration specified in the SQL table?

Thanks, Helen


SQL Server Package Configuration..driving me nuts.



I just started developing a package in SSIS 2005 and I have it working (sort of) but I am trying to get it to work using package configurations. Between errors encountered between ProtectionLevels and Configurations, I've been going around in circles. I would really like some detailed clarifications and hopefully solutions to the issues I'm having now.

I have a package with a single "Execute Sql" task which for the purposes of discussion uses a connection manager 'CONN_ADO' which happens to be an ADO.Net connection. The task has defined variables etc, but the only configurable value for this task is the ConnectionString and I opted to store it in my Sql Server DB called MAINDB via package configurations. In order to add this configuration to Sql Server, I had to first create an OLEDB connection to MAINDB called CONN_OLEDB. Using this connection, I was able to create an SSIS Configurations table in MAINDB and store the connectionstring property for CONN_ADO. I made sure the configured value for CONN_ADO in the SSIS configurations table has the password specified in it and the package works perfectly fine under this setup when the ProtectionLevel is at its default , EncryptSensitiveWithUserKey.

Of course, that was too easy..The default protectionlevel was less than ideal in an actual development environment so I changed the Protection

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

Accessing SharePoint 2010 Data with Server-Side APIs

Accessing SharePoint data in server-side solutions is one of the most common tasks that you will perform as a SharePoint developer. SharePoint 2010 provides powerful server-side APIs that enable you to retrieve, add, edit, and delete SharePoint data programmatically. SharePoint 2010 also includes the new LINQ to SharePoint technology that enables you to work with SharePoint data efficiently and easily.

Sorting Data in a GridView Web Server Control

The GridView control provides built-in sorting functionality without requiring any coding. You can further customize the sort functionality of the GridView control by using custom SortExpression property values for columns as well as by using the Sorting and Sorted events

Frequently Asked Questions - SQL Server Data Types

This is good and precise introduction and Frequently Asked Questions - SQL Server Data Types

server side does not save data when using POST data to another URL using WebRequest


 I am using a IIS 7.5 on Windows 7. I have a simple application that uses System.Net.WebRequest to POST data to another URL (in fact, the server side URL is on my local computer and it is hosted in IIS 7.5).

  1.  The problem is the server does not save data from client although on the client side codes when I debug and I see the OK message return from the code line

   response.StatusDescription; // this has value of "OK"
2. How can I debug the server code side where to receive the request from client side who posts the data ?

The following is the codes for client who posts data using System.Net.WebRequest, and codes for server side who received posted data to save to database.

Client side codes: // also run on my local computer

public string PostIntegrationXml(XmlDocument xmlSubmitDoc)




OData and AtomPub: Building an AtomPub Server Using WCF Data Services


Learn how to use the AtomPub protocol to pump up your blogs. Chris Sells includes a practical demonstration of mapping so you can expose a standard AtomPub service from a Web site and use Windows Live Writer to provide a rich editing experience against the service.

Chris Sells

MSDN Magazine August 2010

Sync Up: Manage Your Data Effectively With The Microsoft Sync Framework


Synchronization Services for ADO.NET provide a set of tools to help you synchronize data between two database sources, synchronize files between machines, and synchronize with an RSS or ATOM feed. Learn more here.

James Yip

MSDN Magazine April 2009

Under The Table: Spatial Data Support In SQL Server 2008


New spatial data support in SQL Server 2008 opens the door to mapping and querying geometric and geographic data, allowing you to build exciting new applications.

Bob Beauchemin

MSDN Magazine February 2009

Test Run: Configuration Testing With Virtual Server, Part 2


Because Virtual Server is built upon a set of COM modules, you can automate the creation and testing of virtual machines. Here we use Windows PowerShell to run the tests.

Dr. James McCaffrey and Paul Despe

MSDN Magazine December 2008

Hierarchy ID: Model Your Data Hierarchies With SQL Server 2008


Here we explain how the new hierarchyID data type in SQL Server 2008 helps solve some of the problems in modeling and querying hierarchical information.

Kent Tegels

MSDN Magazine September 2008

Test Run: Configuration Testing With Virtual Server, Part 1


This time James McCaffrey sets up a virtual environment to use for configuration testing to introduce you to software configuration testing with Microsoft Virtual Server

Dr. James McCaffrey

MSDN Magazine September 2008

Data Services: Develop Robust and Scalable Apps with SQL Server Data Services


Here the author introduces SQL Server Data Services, which exposes its functionality over standard Web service interfaces.

David Robinson

MSDN Magazine July 2008

SQL Server: Uncover Hidden Data to Optimize Application Performance


SQL Server 2005 gathers a lot of useful data that you can use to identify areas where database performance can be improved. Here's what you need to improve performance.

Ian Stirk

MSDN Magazine January 2008

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend