Blogs
Welcome to EMC Consulting Blogs Sign in | Join | Help

Welcome to EMC Consulting Blogs

SSIS Junkie

SSIS: Dynamic modification of SSIS packages - Part II

Yesterday I posted a blog entry that explained some of the options available for dynamically changing SQL Server Integration Services packages at runtime. Paul Shotts posted a comment stating that in DTS he often used the "Dynamic Properties Task" to change the source and destinations of his data pumps and asked whether he was now prohibited from doing the same in SSIS. Happily the answer is no, this can still be done. The "Dynamic Properties Task" has dissappeared but has been extended by the provision of Configurations.

An SSIS configuration allows you to change properties of your variables, connection managers (analogous to a connection in DTS) & control-flow tasks at runtime. This sounds similar to property expressions which I mentioned in my previous blog entry on this subject although there are differences between configurations and property expressions. The main ones being:

  1. Configurations change properties to explicit values. Property expressions can change them to explicit values or values calculated by other means
  2. Property expressions are attached to control-flow tasks and execute when that task executes. Configurations affect the package prior to the package beginning to execute

Configurations are excellent candidates to solve Paul's problem of changing the location of his sources and destinations at runtime. This is especially useful when moving packages between development, test and live environments. I have previously written an article which explains how to do just that which can be found at SQLIS.com.

Hope this helps to explain the concept of configurations a little.

Kirk Haselden has more information on his blog here.

Configurations are set to be one of the most useful features of SSIS, I can't think that anyone will be implementing SSIS without using them!

-Jamie

 

Published 01 March 2005 17:21 by jamie.thomson

Comments

 

TrackBack said:

Expressions: Part III
March 1, 2005 21:49
 

jamie.thomson said:

Thank you for the informative and helpful response!
March 2, 2005 08:42
 

TrackBack said:

May 17, 2005 16:28
 

Ankit said:

Hi,

You had told about using Configuration as a substitute of Dynamic Properties Task. Please tell me how to use Configuration in SSIS as a substitute of Dynamci Properties Task.

January 19, 2007 10:24
 

Evan said:

Hi,

In a Test Database Server where should be the XML Configuration File placed (if I use XML Configuration), when we import the packages.

I have also seen that the SQL Server Configuration [SSIS Configuration Table] is not getting referred by my packages in the test environment. It takes the values that we have used during deployment. Can you please advice what could solve this issue.

Thanks

Evan

March 14, 2007 14:01
 

Natasha said:

Hello,

Thank you for the great posts, I would like to have your advice on this:

I am working on a POC, my goal is to transform the data based on dynamic configurations, so when there is a change I don't need to change anything accept the configurations.

Example:

If I have a destination filed called DatePart, i want the package to read "day(MyDate)" from my config table and transform the MyDate accordingly.

That is doable I can read "day(MyDate)"  from my config table into a variable , and assign the variable to the property expression of my derived column task .

But I’ll end up having variables as many as columns. Is there a way to make the derived column task add new derived columns in run time? Based on a config table /file, or should I build my own custom component??

thank you in advance

July 3, 2007 14:32
 

jamie.thomson said:

Natasha,

It is impossible to change the "shape" (i.e. columns, columns names, column types) at runtime. period. Nevertheless, there is a workaround which is talked about here: http://sqljunkies.com/WebLog/knight_reign/archive/2005/12/31/17731.aspx

I don't really understand why having many variables is a problem tho. of course, its up to you.

-Jamie

July 3, 2007 18:21
 

Natasha said:

thank you :)

July 5, 2007 12:52
 

Mikel said:

Hi there,

I had developed a package and store some configurations (email addresses, connection strings) on a XML config file. An environment variable is used to point the path from the package.

The package is launched by a SQLServer Job.

It works fine at deployment time, the package read the values and start working with them, but at RUNTIME, if I make any change to the config file, it seems not to change the values inside.

I've tried restarting sqlagent and ssis services. Also I have included the Environment variable into the /CONFIG parameter in the SQLServer Job Step Command Line, but nothing happens. It seems that cannot read the configuration at runtime (it does at deployment time) or that there is some kind of cache running there.

Is anybody having the same problems

Thanks

Mikel

July 10, 2007 11:56
 

Peter said:

Mikael,

Have you solved your runtime problem ?

Has anybody had a problem like Mikel (see above) and solved it?

Tnx in advance.

/Peter

April 14, 2008 12:02
 

Jonathan B. said:

Is there a solution to using a (Local).Config database.  As Evan said, works in Debug but not on a job on Dev or LIve.  Just to confirm that connections are Configured but these are not used when package is executed in a job, using SSIS option not dtexec. (Using dtexecui shows original connections in command line).

Thanks

_________________

Evan said:

Hi,

...

I have also seen that the SQL Server Configuration [SSIS Configuration Table] is not getting referred by my packages in the test environment. It takes the values that we have used during deployment. Can you please advice what could solve this issue.

Thanks

Evan

March 14, 2007 14:01

_________________

April 28, 2008 12:50
 

jamie.thomson said:

Jonathan,

If you want to use a local config database then how about pointing to localhost or (local)

Help on (local) and localhost

http://pragmaticworks.com/community/blogs/brianknight/archive/2008/04/25/local-times-out-in-sql-server-2005.aspx

-Jamie

April 29, 2008 12:01
 

Work from home. said:

Work from home. At home work http. Phoenix work from home transcription. Work at home internet business opportunity.

May 20, 2008 22:01
 

Suraj said:

Hi all,

 I am new to SSIS. Please give me detail about how to assign a value to variable and how to retrive value from variable.

Give me step by step explanation.

is it as simple as

dim a as int

a=5

b=a+1

It might me stupid Q for you.

But this ll be great  help for me

Thanks in advance

June 19, 2008 13:59
 

jamie.thomson said:

Suraj,

I don't know what you mean by retrieving a value. Wherever it gets referenced, the value is used. That's as simple as it gets.

If you want to explicitly assign a value to a variable based on another variable(which is what you example does), use an expression on the value property of the variable. The expression will be:

@[User::a] + 1

If you don't know how to do this then I suggest you invest some time really getting to know SSIS.

-Jamie

June 19, 2008 14:08
 

oyunlar said:

I've tried restarting sqlagent and ssis services. Also I have included the Environment variable into the /CONFIG parameter in the SQLServer Job Step Command Line, but nothing happens. It seems that cannot read the configuration at runtime (it does at deployment time) or that there is some kind of cache running there. thanks

June 27, 2008 17:29
 

Alok Singh said:

Hi All,

I have a package which has an ODBC connection.I have created an XML Configuration to store username and password and its working fine.

Now My ODBC connection is dynamic and I pass name of DSN connection through variables in the packge which is again binded with connection using expressions.

Now Since My xml file doesnt get updated with this new connection string I wont be able to fetch data from this new database.

Is there any way where I can update the xml file at runtime with this new value ?

Thanks in advance

July 23, 2008 15:12
 

Alok Singh said:

Hey ... I a still waiting for answer ... Can someone help me ?

July 28, 2008 12:27
 

jamie.thomson said:

If the connection string changes from execution to execution then a config file is the wrong solution.

Investigate the /SET option of dtexec.exe, this is more suitable for your scenario.

-Jamie

July 28, 2008 12:35
 

Hans Brouwer said:

Hi,

Been testing Package Configuration and run into some questions, which are not answered in the several articles on this subject.

I prefer to use a SSIS Configurations table in a dedicated database, which exists on every (important) server. I am trying to find out which values in this table I need to change. I am mainly concerned with ConfiguredValue and PackagePath. I know I have to change ConfiguredValue when deploying the package on another server then my developmentserver. I cannot find, however, if I have to change anything in attribute PackagePath. I do see references to the local environment, like servername:

\Package.Connections[PWSR01NL.DBeheer].Properties[ServerName]

If I change PWSR01NL.DBeheer into NewServername.DBEheer, which is where the local SSIS Configurations tables resides, nothing significant changes. When running the package with a job I receive a (justified) error, that it cannot login to my FIRST server, PWSR01NL. I can only conclude that these new configurations values are NOT read at runtime.

What could be the cause for this? Do I miss something here?

BTW, I did the example at SSIS.com and this worked fine, but is was done with an XML file, which is not quite the same in structure and values passed.

July 31, 2008 14:23
 

jamie.thomson said:

Hans,

Are you trying to tell the package where the configuration table is by changing a value in the configuration table?

Surely you can see that that wouldn't work. You're into a chicken or egg situation.

You need to tell the package where the configuration table resides, a good way of doing this is to use an indirect configuration (lots of stuff about that on the web and in Books Online).

Or have I misunderstood?

-Jamie

July 31, 2008 17:47
 

Steve Ingham said:

Apparently, my SSIS packages are not reading the configuration table for run-time values either.  In a development environment, I used the configuration wizard to store the initial values for (SQL) Server and initial catalog. Packages running for several months using dtexec.  Now when moving to a test environment, I change the values in the table and execute packages, from both the IDE and DTExec (using /set for configuration location).  In both cases, only the previously, hard-coded values are used as verified by a simple script reading the connection string after all the data processing.  This problem of not reading the configuration table seems to be a somewhat common problem above, but has anybody discovered the solution?  What switch did I not set correctly missing?

August 4, 2008 21:11
 

Steve Ingham said:

Answer to using SQL Server configurations: An official MS bug - http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=12

Bottom line is to use xml configurations.

2.25 years and still no fix!

Type   Bug   ID   126797

Status Active Access Restriction Public

Opened By Piquet Blocking Issue No

Opened 5/14/2006 Submission Language English

August 5, 2008 21:28
 

Vicodin. said:

Vicodin user message board. Vicodin. Symptoms vicodin addiction. Overseas vicodin.

May 30, 2009 03:41
New Comments to this post are disabled

This Blog

Syndication
EMC2, EMC, and where information lives are registered trademarks of EMC Corporation.
All other trademarks used herein are the property of their respective owners.                  
© Copyright 2009 EMC Corporation. All rights reserved.                                                    
Powered by Community Server (Personal Edition), by Telligent Systems