Easy Package Configuration

by Guest 30 Nov 2004 14:00

(By Jamie Thomson)

One of the age old problems in DTS is moving packages between your development, test and production environments. Typically a series of manual edits needs to be done to all the packages to make sure that all the connection objects are pointing to the correct physical servers. This is time consuming and gives rise to the possibility of human error, particularly if the solution incorporates many DTS packages. Many companies have provided their own custom solutions for managing this problem but these are still workarounds for a problem that was inherent in DTS.

Happily, Integration Services (IS) now provides a solution to this problem – Package Configurations. Package configurations are a mechanism for dynamically changing properties of your IS objects and components at run-time using values that are stored externally to the package. There are a number of methods to store and pass these values to a package:

  • An XML file
  • Environment variable
  • Registry settings
  • Parent Package Variable

The beauty of an XML file is that each of your packages can point their package configurations at the same XML file which means settings that are pertinent to all packages only have to be changed in one place. In the case of using an XML file to store the values, as long as the XML files are stored in the same place on each environment, (e.g. C:\PackageConfigs\Environment.dtsConfig) it is not necessary to do any editing of the packages when they are moved between environments. This is an example of a direct configuration. An indirect configuration uses environment variables to store the configuration values.

The simplest usage of package configurations would be to store the name of your database server that will be the destination for all your data flows and it is this situation that will be demonstrated herein by use of an XML configuration file. Another method of achieving the same aim would be to store the name of your database server in an environment variable. The environment variable simply needs to be edited on each separate environment.

Setting up your package

First we will need an IS package to which we will add a package configuration. This demonstration will use the simple concept of importing a text file into a SQL Server table.

The text file is a comma-separated-value (CSV) file and should contain the data below, with a file name of PersonAge.txt.

1,Joe Bloggs,27
2,Mary Smith,26
3,Fred Jones,28

The destination table will be called dbo.PersonAge. It will reside in a database called DataStore. Use the following script to create the database and table:

CREATE DATABASE DataStore
GO

USE [DataStore]
GO
SET ANSI_NULLS ON SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[PersonAge](
    [PersonAgeKey] [int] NULL,
    [Person] [varchar](35) NULL,
    [PersonAge] [int] NULL
) ON [PRIMARY]
GO

It is assumed that you are familiar enough with IS to build this package so this article will not explain how to do this in detail except to ask that you build your package thus:

  • An Execute SQL task, named Truncate destination. The SQL command is TRUNCATE TABLE dbo.PersonAge.
  • A Data Flow task, named Import File.
  • In your control flow, drag a precedence constraint arrow from Truncate destination to Import File.

Your control flow should look like this:

Now build the data flow inside our Import File task:

  • A Flat File connection called Source pointing at your recently created CSV
  • An OLE DB Connection called Destination pointing at your recently created database DataStore.
  • Import File data flow requires a Flat File Source component pointing at Source. In the advanced editor, Input and Output Properties tab, change the DataType property of external columns Column 0 & Column 2 to DT_I4. Do the same to the output columns Column 0 & Column 2 as well.
  • Import File data flow requires an OLE DB Destination pointing at Destination. In the editor select the dbo.PersonAge table as the destination.
  • The output from the Flat File Source is the input to the OLE DB Destination.

Your data flow should look like this:

At this stage you should be able to run the package successfully.

Setting up a package configuration

Now for setting up the package configuration. Its very easy to do but brings tremendous flexibility to your packages. With a package configuration you can edit your package properties, variables, connections and the properties of your control flow tasks (termed executables) at run-time. Note that you cannot edit the properties of your data flow components.

  • On the menu bar, point to DTS, Package Configurations… or right-click on the control flow design surface and select Package Configurations….
  • In the Package Configurations Organizer click Enable package configurations and click Add…
  • Click through the welcome screen and in the Configuration Type combo select XML Configuration File.
  • In the space for Configuration file name type C:\PackageConfigurations\Environment.dtsConfig and click Next
  • In the object tree browse to Connections.Destination.Properties and check the InitialCatalog & ServerName properties. Click Next
  • Give your configuration a name and click Finish. See…easy!!!

Your package will now pick up the values for the 2 properties at run-time. If you open the XML file in a text editor you will be able to see that the properties are currently set to whatever they were set to prior to building the package configuration.

Now you could easily move this package (and any other packages in your application) to a completely new environment and all you would have to do is change 1 property in C:\PackageConfigurations\Environment.dtsConfig. Pretty nifty! If you wanted to you could even dynamically populate the name and location of your source file at run-time.

Summary

This is a short introduction to package configurations in order to demonstrate the use of them. Package configurations are used to alter the state of your package at run-time therefore enabling the IS developer to build dynamic packages without having to write custom code.

Download the pre-built demonstration material (7KB) Easy Package Configuration.zip.

Comments (11) -

11/4/2008 11:49:22 AM #

vinod

In DTS package you could use a query to set the value of a connection. For eg., there is one gateway database and 2 other databases of which one is a primary and other is a standby database.  If there is a failover status changes from primary to standby.  At any point gateway database has information about the current primary server.

Inside a dts package I can query this database and set the connection so that data is always pulled out of primary database.  How can I do this in SSIS?  Or how can I use queries to alter the value of connections in SSIS?

This is my 2nd day on SSIS.  Hope you can help me.

thank you
Vinod

vinod United Kingdom

11/5/2008 12:44:42 PM #

Darren Green

If you take a look at the Execute SQL Task (www.sqlis.com/post/The-Execute-SQL-Task.aspx) post, you’ll see how you can store the result of a SELECT query into a variable. The variable can then be used in a Property Expression (technet.microsoft.com/en-us/library/ms141698.aspx) on the Connection, perhaps the ConnectionString property to dynamically change the server that the connection points at.

Darren Green United Kingdom

11/11/2008 9:56:03 PM #

Mahesh Vijayamohanan

Thanks!!! This helped

Mahesh Vijayamohanan United States

2/18/2009 10:52:05 PM #

STeve Tahmosh

Hi,
Thanks for the intro to package configurations.
I'm fairly new to SSIS, but have managed to get two packages working together sharing a Package Configuration.  I have run into a problem trying to schedule the package through SQL Agent.
We have 15-20 feeds that we have to load.  The format of each load is different.

The schedule for each feed is such that there can be a wide window in which the feed can come in.
The user can change the schedule of the feed (it is database-driven), also.
I developed my app as 2 packages, a "Scheduler", and a "Loader".
While there is one Scheduler, there will be 15-20 Loader Packages.
The Scheduler is configured with:
1.  Log File Path (great example on dynamic log file names in the Expert SSIS book!)
2.  Database Connection info
3.  Source Feed info (e.g., Feed Cd, Inbound Folder, Staging Folder, etc.)

The Job of the Scheduler is to:
1.  Check if the feed should run today.
2.  Check the Start time to begin watching for the feed, and the length of time to wait for the feed before aborting

If the Feed should run today, the Scheduler invokes an "Execute Package Task" to call the Loader

The Scheduler is also configured with the Name of the Loader Package to invoke
The Scheduler and the Loader Package are both deployed, and are intended to share a single Package configuration file.

I've benefited obviously from the FileWatcher Task provided at this site!

The Scheduled Job is a single Step
   A SQL Server Integration Services Package Step
    The Package is the "Scheduler" Package
    I spectified a Package Configuration File for both the "Scheduler" Package and the "Loader" Package (same config file)

When I run the job, the Agent finds the Package configuration, and correctly runs the Scheduler Package.

However, the job fails when the Loader Package tries to run using the "Default" package configuration from the Development environment.

Is there a way to use the Execute Package Task, and pass in the Configuration file to use when executing the package?

I investigated using "Package Parent Variables", but that is cumbersome because:
  1.  There are a half dozen variables – requiring a hafl dozen configurations
  2.  This precludes running the Loader package stand-alone (it is self-sufficient save for the "Connection-related variables")

Any thoughts appreciated.

Thanks,
STeve

STeve Tahmosh United States

3/31/2009 10:52:58 AM #

Chandra Bose

Hi,
Setting up the package using Configuration is quite useful but we would more be appreciated if you could provide configuration using "SQL Server". No blog or site has provided any hint to use this configuration in order to start with. Hopefully we all would be interested if you could provide us step by step process by quoting examples like when packages deployed from development to test, test to production.

Thanks in advance.

Chandra Bose India

4/1/2009 8:25:12 AM #

Chris Gaisberger

Hi,
I've successfully set up an xml config for a dataflow task using an xml source. The only thing I miss is how to config the path to the xsd file in the .dtsconfig. Unfortunately I can't use inline schema for I don't have any influence on the format of the xml source file. Any ideas?

Best regards, Chris

Chris Gaisberger Austria

4/1/2009 8:29:45 AM #

Chris Gaisberger

Sorry... I found the solution by myself... I mixed up the xml connection manager with the xml source. The xml source of course has a config setting for the xsd.

But thanks anyway, great site :)

Regards, Chris

Chris Gaisberger Austria

7/14/2009 8:11:34 PM #

Dave Powell

For some reason I keep getting the following error.  I have saved the sensitive info with a password:

Error  3  Validation error. Data Flow Task: OLE DB Destination [217]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "removed" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  

Dave Powell United States

7/24/2009 3:41:17 PM #

chand

I am new to SSIS, pkg configuration. I have a question i have a ssis package connected to a database in development database ,where my pkg and config is working fine. But i have to move to QA but i dont have the database in QA server. Can i configure my package to point to development server database  and package which is in QA server. Basically its a cross domain issue.

Thanks
chand

chand United States

3/23/2011 4:54:15 PM #

Dimple

hi,
i'm newby in SSIS ETL.i make ssis package using the return resultset with variable(object type).my DTS can run smoothly & correctly.When i generate the configuration file, it show errors and can't generate.if i uncheck the value of the  variable(object type),it generate config file smoothly.Any ideas? Do i miss anything?
regards,
cherry

Dimple United States

3/31/2011 2:58:59 PM #

Carlos

Hi friends,

package configurations are ok in the most of cases. but in my case the parameters should be given by users (company id, budget id, year id,...),  who do not have the obligation to understand xml files.

In SQL2000 I used ini files, which were easy to handle for users.

How can I offer the user an easy way to provide parameters for the execution of the package?

Carlos Chile

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Widget Amazon Book not found.

Object reference not set to an instance of an object.X

RecentComments

Comment RSS