Connection string expression is not evaluated.

Question

Hi all,
I have a series of extracts against an oracle database all being executed by Execute Package tasks in a master package. These EP tasks are contained in a Sequence container and are allowed to execute concurrently. The path to each package being executed (the connection string) is determined by an expression set in the connection manager and this expression is based on a root folder variable loaded from an XML configuration file.

The expression looks like this:

@[User::PackageFolder] + "\\PackageName.dtsx"

And the PackageFolder variable is based on the RootFolder variable loaded from the config file:

@[User::RootFolder] + "\\PackageStore"

When I initially add these EP tasks in BIDS on my local machine and create the file Connection Manager , the path to the packages is on the c drive and the root folder variable in my local config file also points to the C drive.

When I move this master package to our development environment, the packages are now located on the d and the RootFolder variable in the dev config file points to the D drive.

Here's the problem: every once and a while, and it seems to be completely random, an EP task connection manager will seem to not evaluate the expression and look for the package on the C drive. If I run all the EP tasks in sequence, this problem does not occur.

I really have no idea what's going on here, and no idea how to track down the cause of the problem. If anyone has any suggestions (or, better yet, concrete solutions ;)), I would appreciate it. If you need more detail, let me know.

Thanks

Edit: Forgot to mention that all the child packages load the same config file as the parent package, and there are no parent package configurations defined.

Answer 1

HI indignatz
few questions
1. when U say u move the packages to different environment : How are u moving the packages is it simply zip copy paste ... and transfer or U are communicating the manifest folder [deployment under bin dir ...] ideally speaking u should be handling package migration via manifest only

check out http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/7c2b83b5-0568-4414-8804-ec0af0c153dc/
2. when  you say that variables are updated / loaded by config file {i guess u are using the xml configurations as ssis configurations ....}
when deploying [running manifest ] the packages check out where u store package dependencies .... edit variables in that config file and reload ur package in bids and see if the ssis package configurations are picking ip the right file from the configuration string ...
thread for more on package configurations
here are few check points you can do :
1. after deploying the packages to other environment open the packages in bids .. and check the the SSIS-> Package configurations check the configuration string of ur package configurations
for example if ur configuration string when u developed package was C:\myConfig.dtsConfig
and when u deployed the manifest at other environment and selected package dependencies as D:\Config
then after installation of manifest open package in BIDS the ssis configuration string should be D:\Config\myConfig.dtsConfig
and the variable values updated in D:\Config\myConfig.dtsConfig should and will be reflected in your package .....

Hope that helps .....

__________________________________________________________
Please mark the post/s answered / helpful if they answer ur question -- kunal

Answer 2

I have no issue with setting up config files. I have a number of them in place and they all work perfectly well and, as I said, this is a random occurrence.

The configuration files are correct and the packages are also set up correctly, but every once and a while, if I run the execute package tasks concurrently, one task, and it's not always the same task, will fail to evaluate the connection string expression.

Or, maddeningly, all of them will succeed.

Has anyone else seen this, or have some idea of what might be going on here?

Thanks

Answer 3

I've never seen a config file fail to set a property properly.

A debugging thought: have you checked the variables list for duplicate copies of the variable at different scopes? Check the variable list by choosing the option to show all variables and see if you have the same variable scoped at different levels of the package.

Cheers, James

---

James Beresford @ http://www.bimonkey.com SSIS / MSBI Consultant in Sydney, Australia

Answer 4

I've run into a similar issue when trying to connect to multiple AS400 sources. Try setting the DelayValidation property for all the connection managers and EP tasks to True and see if that helps.

-Victor

---

Victor Rocca - LiveLogic

Answer 5

Thanks guys.

James, no scope issues.

Victor, I think you might be on to something there. I'm going to give that a try and I'll let you know if it works out.

-Mark

Answer 6

Victor that did indeed seem to do the trick. I've had no failures since implementing your suggestion.

Thanks
-Mark