Parent Package Variables and SQL Package Store

Hello All,

I have a Master Package which calls a group of other packages out on the SSIS Package Store using parent / child and a number of variables. These seem to work when passing various audit information (audit key, record counts, etc) but when I try to pass a variable for the connection string and assign it via an expression to the connection manager, I get the Master Package writing to the correct database and the children package writing to their "default" database which is supplied as the default value in the variables which should be populated by the parent task.

i.e., I end up with my master package audit information in database a and child package audit and transactions in database b, even though the packages associated with these transactions are supposed to be reading the connection string from a parent variable. Any clues or suggestions?

Edit: It is still passing along the correct parent audit information and record counts, it's just not connecting to the correct database by the variables holding the connect string

Thanks for your time.

After your issue the other day, it occurred to me that I do something differently, yet achieve the same thing.

I pass in pieces of the connection strings to separate variables. (ServerName, DatabaseName, etc...)

Then, on the connection manager objects, I use their expressions to build its connection string based on those variables. So I'm not passing in a connection string -- I'm just passing in pieces. Then the connection manager's expression for "ConnectionString" takes it from there. Does that make sense?|||

yeah, that makes sense... I've been thinking about doing that for about a day now myself, although I was just wanting to get this to work first before going through with it. I think I will go ahead and change things over to build up the string dynamically, that way I won't have to retype everything in the set values tab every time I want to tinker with which database or server it is going to.

Right now I am building a test package that is passing the connection string and it works fine, however the test package is passing it to a pacakge on the file server (not in the sql package store). There shouldn't be any difference in how a package accepts parent variables between a file server executed package and a sql store executed package should there?

|||

Well, I just spent a few hours converting those packages over to store the data source, initial catalog, name and password as seperatre variables and build the string up in the expression for the connection string. When I ran the package it was still pointing to the "default" values defined in the child package, even though I had changed the parent to point to a different data base within the source...

Any ideas?

|||

EWisdahl wrote:

Well, I just spent a few hours converting those packages over to store the data source, initial catalog, name and password as seperatre variables and build the string up in the expression for the connection string. When I ran the package it was still pointing to the "default" values defined in the child package, even though I had changed the parent to point to a different data base within the source...

Any ideas?

Are you certain that the parent package variable configuration is set up properly? You may want to try adding a Script Task to your control flow, and within it using a MessageBox to pop up the value being configured just to validate.

Personally, I solve this problem with XML file configurations. I have one dtsConfig file that drives the ConnectionString property of the connection, and have connection managers with the same name in my master and child packages, and this works great for me.

|||Also try setting DelayValidation to true on your connection manager that you are configuring.|||

I am fairly certain that I have them set up correctly in that I am also passing along other information such as audit id, filename, recordcount, etc and that is all populating correctly. It seems like it has predetermined what the connection managers connection string will be before the package receives the parent variable configurations...

|||

Phil Brammer wrote:

Also try setting DelayValidation to true on your connection manager that you are configuring.

Didn't seem to help any...

|||

EWisdahl wrote:

Phil Brammer wrote:

Also try setting DelayValidation to true on your connection manager that you are configuring.

Didn't seem to help any...

Then make sure your parent package configurations are working correctly. Make sure you're mapping to the .Value parameter of your child variable. Also, CaSE matters, I believe. And I always just type in the name of the variable. No "User::" or "System::" prefix.|||

Double checked and triple checked. As I said, I am able to pass off certain values (auditkey, etc) no problem. As a matter of fact, I am able to pass the same connection string (or portions thereof to piece together into the connection string) if I am passing it to a package via the file system, it only seems to come up when passing parent package configuration variables from a parent to a child stored on the SSIS package store.

On a positive note, the set values through sql agent job maintenance works fine for the master package now ... If only it would pass these to the child packages (or if the child packages would pick it up from the master's "default") igh:

|||

After reading another thread I think I may have described something a bit inaccurately. I am storing these packages on the SSIS Server under the msdb subfolder (i.e. connect to integration server, click subnode stored packages, msdb, packagefolder -> import) as opposed to the package store (same as above but click on the file system within the stored packages node). Would that make any difference?

|||

Question: Are you using any other configurations in your child packages, besides the Parent Package Variables? If so, can you describe those?

|||

Nope, just the parent package variables.

|||

Well, i am currently facing something like this...

And i am starting to think that Parent Variables are somehow desync with the child package execution...

In my situation i have Execute SQL Task at the beggining of the package registering the inicial log record... and its beeing inserted with the default values on my child package instead of using the configured parent package values!

Later on i have that kind of connectionString mechanism and its working right... i think its a matter of delay... probably if you try putting a script task before using the connection, you will get your parent variables in time.

Had you tried it?

Just a though...

Regards

Parent Package Variables and SQL Package Store

Hello All,

I have a Master Package which calls a group of other packages out on the SSIS Package Store using parent / child and a number of variables. These seem to work when passing various audit information (audit key, record counts, etc) but when I try to pass a variable for the connection string and assign it via an expression to the connection manager, I get the Master Package writing to the correct database and the children package writing to their "default" database which is supplied as the default value in the variables which should be populated by the parent task.

i.e., I end up with my master package audit information in database a and child package audit and transactions in database b, even though the packages associated with these transactions are supposed to be reading the connection string from a parent variable. Any clues or suggestions?

Edit: It is still passing along the correct parent audit information and record counts, it's just not connecting to the correct database by the variables holding the connect string

Thanks for your time.

After your issue the other day, it occurred to me that I do something differently, yet achieve the same thing.

I pass in pieces of the connection strings to separate variables. (ServerName, DatabaseName, etc...)

Then, on the connection manager objects, I use their expressions to build its connection string based on those variables. So I'm not passing in a connection string -- I'm just passing in pieces. Then the connection manager's expression for "ConnectionString" takes it from there. Does that make sense?|||

yeah, that makes sense... I've been thinking about doing that for about a day now myself, although I was just wanting to get this to work first before going through with it. I think I will go ahead and change things over to build up the string dynamically, that way I won't have to retype everything in the set values tab every time I want to tinker with which database or server it is going to.

Right now I am building a test package that is passing the connection string and it works fine, however the test package is passing it to a pacakge on the file server (not in the sql package store). There shouldn't be any difference in how a package accepts parent variables between a file server executed package and a sql store executed package should there?

|||

Well, I just spent a few hours converting those packages over to store the data source, initial catalog, name and password as seperatre variables and build the string up in the expression for the connection string. When I ran the package it was still pointing to the "default" values defined in the child package, even though I had changed the parent to point to a different data base within the source...

Any ideas?

|||

EWisdahl wrote:

Well, I just spent a few hours converting those packages over to store the data source, initial catalog, name and password as seperatre variables and build the string up in the expression for the connection string. When I ran the package it was still pointing to the "default" values defined in the child package, even though I had changed the parent to point to a different data base within the source...

Any ideas?

Are you certain that the parent package variable configuration is set up properly? You may want to try adding a Script Task to your control flow, and within it using a MessageBox to pop up the value being configured just to validate.

Personally, I solve this problem with XML file configurations. I have one dtsConfig file that drives the ConnectionString property of the connection, and have connection managers with the same name in my master and child packages, and this works great for me.

|||Also try setting DelayValidation to true on your connection manager that you are configuring.|||

I am fairly certain that I have them set up correctly in that I am also passing along other information such as audit id, filename, recordcount, etc and that is all populating correctly. It seems like it has predetermined what the connection managers connection string will be before the package receives the parent variable configurations...

|||

Phil Brammer wrote:

Also try setting DelayValidation to true on your connection manager that you are configuring.

Didn't seem to help any...

|||

EWisdahl wrote:

Phil Brammer wrote:

Also try setting DelayValidation to true on your connection manager that you are configuring.

Didn't seem to help any...

Then make sure your parent package configurations are working correctly. Make sure you're mapping to the .Value parameter of your child variable. Also, CaSE matters, I believe. And I always just type in the name of the variable. No "User::" or "System::" prefix.|||

Double checked and triple checked. As I said, I am able to pass off certain values (auditkey, etc) no problem. As a matter of fact, I am able to pass the same connection string (or portions thereof to piece together into the connection string) if I am passing it to a package via the file system, it only seems to come up when passing parent package configuration variables from a parent to a child stored on the SSIS package store.

On a positive note, the set values through sql agent job maintenance works fine for the master package now ... If only it would pass these to the child packages (or if the child packages would pick it up from the master's "default") igh:

|||

After reading another thread I think I may have described something a bit inaccurately. I am storing these packages on the SSIS Server under the msdb subfolder (i.e. connect to integration server, click subnode stored packages, msdb, packagefolder -> import) as opposed to the package store (same as above but click on the file system within the stored packages node). Would that make any difference?

|||

Question: Are you using any other configurations in your child packages, besides the Parent Package Variables? If so, can you describe those?

|||

Nope, just the parent package variables.

|||

Well, i am currently facing something like this...

And i am starting to think that Parent Variables are somehow desync with the child package execution...

In my situation i have Execute SQL Task at the beggining of the package registering the inicial log record... and its beeing inserted with the default values on my child package instead of using the configured parent package values!

Later on i have that kind of connectionString mechanism and its working right... i think its a matter of delay... probably if you try putting a script task before using the connection, you will get your parent variables in time.

Had you tried it?

Just a though...

Regards

Parent Package Variables and SQL Package Store

Hello All,

I have a Master Package which calls a group of other packages out on the SSIS Package Store using parent / child and a number of variables. These seem to work when passing various audit information (audit key, record counts, etc) but when I try to pass a variable for the connection string and assign it via an expression to the connection manager, I get the Master Package writing to the correct database and the children package writing to their "default" database which is supplied as the default value in the variables which should be populated by the parent task.

i.e., I end up with my master package audit information in database a and child package audit and transactions in database b, even though the packages associated with these transactions are supposed to be reading the connection string from a parent variable. Any clues or suggestions?

Edit: It is still passing along the correct parent audit information and record counts, it's just not connecting to the correct database by the variables holding the connect string

Thanks for your time.

After your issue the other day, it occurred to me that I do something differently, yet achieve the same thing.

I pass in pieces of the connection strings to separate variables. (ServerName, DatabaseName, etc...)

Then, on the connection manager objects, I use their expressions to build its connection string based on those variables. So I'm not passing in a connection string -- I'm just passing in pieces. Then the connection manager's expression for "ConnectionString" takes it from there. Does that make sense?|||

yeah, that makes sense... I've been thinking about doing that for about a day now myself, although I was just wanting to get this to work first before going through with it. I think I will go ahead and change things over to build up the string dynamically, that way I won't have to retype everything in the set values tab every time I want to tinker with which database or server it is going to.

Right now I am building a test package that is passing the connection string and it works fine, however the test package is passing it to a pacakge on the file server (not in the sql package store). There shouldn't be any difference in how a package accepts parent variables between a file server executed package and a sql store executed package should there?

|||

Well, I just spent a few hours converting those packages over to store the data source, initial catalog, name and password as seperatre variables and build the string up in the expression for the connection string. When I ran the package it was still pointing to the "default" values defined in the child package, even though I had changed the parent to point to a different data base within the source...

Any ideas?

|||

EWisdahl wrote:

Well, I just spent a few hours converting those packages over to store the data source, initial catalog, name and password as seperatre variables and build the string up in the expression for the connection string. When I ran the package it was still pointing to the "default" values defined in the child package, even though I had changed the parent to point to a different data base within the source...

Any ideas?

Are you certain that the parent package variable configuration is set up properly? You may want to try adding a Script Task to your control flow, and within it using a MessageBox to pop up the value being configured just to validate.

Personally, I solve this problem with XML file configurations. I have one dtsConfig file that drives the ConnectionString property of the connection, and have connection managers with the same name in my master and child packages, and this works great for me.

|||Also try setting DelayValidation to true on your connection manager that you are configuring.|||

I am fairly certain that I have them set up correctly in that I am also passing along other information such as audit id, filename, recordcount, etc and that is all populating correctly. It seems like it has predetermined what the connection managers connection string will be before the package receives the parent variable configurations...

|||

Phil Brammer wrote:

Also try setting DelayValidation to true on your connection manager that you are configuring.

Didn't seem to help any...

|||

EWisdahl wrote:

Phil Brammer wrote:

Also try setting DelayValidation to true on your connection manager that you are configuring.

Didn't seem to help any...

Then make sure your parent package configurations are working correctly. Make sure you're mapping to the .Value parameter of your child variable. Also, CaSE matters, I believe. And I always just type in the name of the variable. No "User::" or "System::" prefix.|||

Double checked and triple checked. As I said, I am able to pass off certain values (auditkey, etc) no problem. As a matter of fact, I am able to pass the same connection string (or portions thereof to piece together into the connection string) if I am passing it to a package via the file system, it only seems to come up when passing parent package configuration variables from a parent to a child stored on the SSIS package store.

On a positive note, the set values through sql agent job maintenance works fine for the master package now ... If only it would pass these to the child packages (or if the child packages would pick it up from the master's "default") igh:

|||

After reading another thread I think I may have described something a bit inaccurately. I am storing these packages on the SSIS Server under the msdb subfolder (i.e. connect to integration server, click subnode stored packages, msdb, packagefolder -> import) as opposed to the package store (same as above but click on the file system within the stored packages node). Would that make any difference?

|||

Question: Are you using any other configurations in your child packages, besides the Parent Package Variables? If so, can you describe those?

|||

Nope, just the parent package variables.

|||

Well, i am currently facing something like this...

And i am starting to think that Parent Variables are somehow desync with the child package execution...

In my situation i have Execute SQL Task at the beggining of the package registering the inicial log record... and its beeing inserted with the default values on my child package instead of using the configured parent package values!

Later on i have that kind of connectionString mechanism and its working right... i think its a matter of delay... probably if you try putting a script task before using the connection, you will get your parent variables in time.

Had you tried it?

Just a though...

Regards

Parent Package Variable visibility when designing packages

I've created an SSIS package that uses parent package variables at several steps in the data flow. However, those parent package variables are only visible during runtime, making debugging the package practically impossible. Let me give you a simplified example:

In the parent package, I have a string variable named "sqlLookup" that looks like this:

"SELECT * from tblTest WHERE city = " + @.city

Also in the parent package is a variable named "city" which I can set to the name of the city that I want to query on. This dynamically updates the sqlLookup variable, which is being evaluated as an expression.

In the child package, I have an OLE DB Source control which is using the "sqlLookup" variable for its query. I have set up my parent package variable configuration, and it works when I run the package.

The problem is this...when I open the child package, I get an error on the OLE DB Source control using the parent package variable, "Command text was not set for the command object". Presumably, this is because the parent package variable is only available during run-time, and not at design time. And, if metadata changes (which it has), I can't get into the OLE DB Source control to edit it, because it throws the error.

So, my question is this: are there any workarounds for this problem? Is there a better way to do this? This seems like such an obvious problem that I'm wondering if I either missed a step somewhere, or if I'm just doing it the wrong way.

Thanks!

Try setting DelayValidation=TRUE on the data-flow task in the child package.

If that fails, set Work Offline on the SSIS menu.

-Jamie

|||

Jamie,

Thanks for the reply, however I'm not having any luck with either of your suggestions. I created a new Project from scratch to simplify the problem.

My Project contains two files, parent.dtsx and child.dtsx.

parent.dtsx contains only an Execute Package task pointing to child.dtsx, and one string variable called sqlGetNames, which is set to " SELECT * from tblCustomers WHERE city = 'Denver' "

child.dtsx contains a single Data Flow, with one OLE DB Source control. It contains one variable, also called "sqlGetNames". I have enabled Package Configurations, and created a Parent Package Variable that should get the value of sqlGetNames from the parent.

However, when I go into my OLE DB Source control in the child package, I get the following error when I tell it to use sqlGetNames as the variable for the SQL command:

Error at Data Flow Task [OLE DB Source[1]]: An OLE DB error has occurred. Error code: 0x80040E0C. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E0C Description: "Command text was not set for the command object.".

I get this error whether or not I have DelayValidation set to False. If I set SSIS to work offline, then I can't set up the OLE DB Source control because there is no connection.

Any other ideas?

|||It may work if you set the child package's variable to have an initial value that works at design time, ie child.sqlGetNames = "SELECT * from tblCustomers"|||

Thanks. That's not exactly what I was hoping for, but it's a useful workaround. I appreciate the suggestion.

Parent Package variable assignment issue in Child Package.

We

have one main package from which 7 other child packages are called. We are using

ParentPackage variables to assign values for variables and database connections.

While the values from ParentPackage variable get assigned to some of the

packages properly, to others it doesn’t assign the value.

For example:

Except for one of the packages the database connection string gets assigned

properly to all other packages.

Similarly, in another package one of the

variables doesn’t get assigned. In other packages it is assigned properly.

We

have checked all the other property values and they are exactly the same.

We cannot make any head or tail of this erratic behavior.

Please Help.

The only known issue I am aware of is that Package configurations based on parent package variables always occour last no matter its place in the configuration organizer.

This represent an issue when you use this method to configure a connection string that would be used by other configuration (when using SQL Server based package configurations). But this should not be the case when it is used to assign variable values, etc.

Make sure the spelling of the variable name in the child package is right; also enable package logging an see is there is any 'Warning' at the begining of the execution log. When a package configuration does not occur a warning is generated. If you are running the package via BIDS review the progress tab as it offers the same info than the logging.

|||Thanks,

The issue has got solved. There was the problem of Case of the variable names only and I also came to know by checking the warnings.

Thanks again.

Parent Package reports failure on errors, but no errors in log

I have a parent package that calls child packages inside a For Each container. When I debug/run the parent package (from VS), I get the following error message: Warning: The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

It appears to be failing while executing the child package. However, the logs (via the "progress" tab) for both the parent package and the child package show no errors other than the one listed above (and that shows in the parent package log). The child package appears to validate completely without error (all components are green and no error messages in the log). I turned on SSIS logging to a text file and see nothing in there either.

If I bump up the MaximumErrorCount in the parent package and in the Execute Package Task that calls the child package to 4 (to go one above the error count indicated in the message above), the whole thing executes sucessfully. I don't want to leave the Max Error Count set like this. Is there something I am missing? For example are there errors that do not get logged by default? I get some warnings, do a certain number of warnings equal an error?

Thanks,

Lee

I never figured this one out. It looks like in a parent/child package model this happens where you will get an "error" even though the child package succeeded. I've checked and there are no data errors (e.g. truncation, ...) or package logic errors. It has happened on two other package sets I've created where there is a parent package calling child packages. The work around has been to bump up the MaximumErrorCounts in the execute package task, the for each container that contains the child packages, and the parent package. Not a good solution but it works.|||

I still continue to see this behavior. I have text file logging setup to catch every event in both the child and parent packages. I have no warnings or errors and the child package runs completely and does what it is supposed to do. But in the parent package, the child "Execute Package Task" fails with a "The Execution method succeeded, but the number of errors raised..." which is the only error message I get in the logs. Again, every event type is set to log.

I can work around this problem by bumping up the MaximumErrorCount on the "Execute Package Task" but what errors is this catching?

Is this a bug? Are there any MVPs on here?

|||I encountered the same error and could not solve it, I had to use the maxerrorcount work around as well

Parent Package reports failure on errors, but no errors in log

I have a parent package that calls child packages inside a For Each container. When I debug/run the parent package (from VS), I get the following error message: Warning: The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

It appears to be failing while executing the child package. However, the logs (via the "progress" tab) for both the parent package and the child package show no errors other than the one listed above (and that shows in the parent package log). The child package appears to validate completely without error (all components are green and no error messages in the log). I turned on SSIS logging to a text file and see nothing in there either.

If I bump up the MaximumErrorCount in the parent package and in the Execute Package Task that calls the child package to 4 (to go one above the error count indicated in the message above), the whole thing executes sucessfully. I don't want to leave the Max Error Count set like this. Is there something I am missing? For example are there errors that do not get logged by default? I get some warnings, do a certain number of warnings equal an error?

Thanks,

Lee

I never figured this one out. It looks like in a parent/child package model this happens where you will get an "error" even though the child package succeeded. I've checked and there are no data errors (e.g. truncation, ...) or package logic errors. It has happened on two other package sets I've created where there is a parent package calling child packages. The work around has been to bump up the MaximumErrorCounts in the execute package task, the for each container that contains the child packages, and the parent package. Not a good solution but it works.|||

I still continue to see this behavior. I have text file logging setup to catch every event in both the child and parent packages. I have no warnings or errors and the child package runs completely and does what it is supposed to do. But in the parent package, the child "Execute Package Task" fails with a "The Execution method succeeded, but the number of errors raised..." which is the only error message I get in the logs. Again, every event type is set to log.

I can work around this problem by bumping up the MaximumErrorCount on the "Execute Package Task" but what errors is this catching?

Is this a bug? Are there any MVPs on here?

Parent Package reports failure on errors, but no errors in log

I have a parent package that calls child packages inside a For Each container. When I debug/run the parent package (from VS), I get the following error message: Warning: The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

It appears to be failing while executing the child package. However, the logs (via the "progress" tab) for both the parent package and the child package show no errors other than the one listed above (and that shows in the parent package log). The child package appears to validate completely without error (all components are green and no error messages in the log). I turned on SSIS logging to a text file and see nothing in there either.

If I bump up the MaximumErrorCount in the parent package and in the Execute Package Task that calls the child package to 4 (to go one above the error count indicated in the message above), the whole thing executes sucessfully. I don't want to leave the Max Error Count set like this. Is there something I am missing? For example are there errors that do not get logged by default? I get some warnings, do a certain number of warnings equal an error?

Thanks,

Lee

I never figured this one out. It looks like in a parent/child package model this happens where you will get an "error" even though the child package succeeded. I've checked and there are no data errors (e.g. truncation, ...) or package logic errors. It has happened on two other package sets I've created where there is a parent package calling child packages. The work around has been to bump up the MaximumErrorCounts in the execute package task, the for each container that contains the child packages, and the parent package. Not a good solution but it works.|||

I still continue to see this behavior. I have text file logging setup to catch every event in both the child and parent packages. I have no warnings or errors and the child package runs completely and does what it is supposed to do. But in the parent package, the child "Execute Package Task" fails with a "The Execution method succeeded, but the number of errors raised..." which is the only error message I get in the logs. Again, every event type is set to log.

I can work around this problem by bumping up the MaximumErrorCount on the "Execute Package Task" but what errors is this catching?

Is this a bug? Are there any MVPs on here?

Parent Package is losing a child package

I have a parent package which calls a number of child packages. Occasionally, I have one child package which starts up and appears to finish but the parent package never gets notification that the child has finished. If I view this in Management Studio's in Integration Services, I can view the packages that are running and only the parent package is running. Because it never gets control back, the next child package will never get started and the parent package will continue to run and never finish.

Any idea what could be happening?

Jarand wrote:

I have a parent package which calls a number of child packages. Occasionally, I have one child package which starts up and appears to finish but the parent package never gets notification that the child has finished. If I view this in Management Studio's in Integration Services, I can view the packages that are running and only the parent package is running. Because it never gets control back, the next child package will never get started and the parent package will continue to run and never finish.

Any idea what could be happening?

You say the child package "appears" to finish. i wonder if that is the case. Take a look at the child package's log file and see if the package container raises an OnPostExecute event (it should be the last row in the file).

-Jamie

Parent package call to Child package

I run into some issues and really need some expert help here.

Here is the problem. I have two packages (parent.dtsx and child.dtsx). Both package have its own configuration file (parent.dtsConfig and child.dtsConfig). The file Child.dtsConfig contains a variable (i.e. "X") that is to be used by Child.dtsx.

Inside parent.dtsx. there is a package-task that calls into Child.dtsx. It worked perfectly well if I run parent.dtsx using Dtexec or from inside SSIS's IDE.

Now I want to programmably call "parent.dtsx" from my C# code. I loaded package using "app.LoadPackage"... Inside C# code, I want to reconfigure Child-package's variable ("X"). I then loaded in "Child.dtsx". However when I run "parent.dtsx" and child.dtsx still loads the original value for "X". The reconfigured value for "X" is not updated.

Please help on how to get around this issue.

Thanks.

Are you changing the configuration file or the value in the package itself? If you change the value in the package, it will be overwritten by the value from the config file at runtime.|||

Your comments are exactly CORRECT...

I load the package and change the values (for both parent and chiled) inside the C# code. Yes, when I kick off parent package from C#, the child package load variables' value from child's configuration file.... This is NOT what I want.. Do you know any way to avoid this ? How do I persist the udpated values ?

|||Change the config file information with your C# code. Or use a SQL Server based configuration and update the table before each child package execution.|||

Phil Brammer wrote:

Change the config file information with your C# code. Or use a SQL Server based configuration and update the table before each child package execution.

Or don't use configurations. If you are running the packages from your code, and you are setting the values each time you load and execute the packages, what purpose are the configurations serving?

|||

Thank you... Your comments are valid suggestions.

However,

first, I have a master package (parent) and many child packages. Each individual child package has its own configuration file (childXXX.dtsConfig) which is an XML file. We did not use SQL as configuration. We want to reuse this entire package suite for two different business domains. In one scenario, we run packages using DTEXEC (or scheduled job) and it worked well. In another scenario, we want to RE-USE the entire package suite from C# code. I want to re-configure each packages inside C# code. And lauch the master package and HOPEFULLY execute all child packages with updated variable values (rather than reading from configuration files because configuration file contains old-values which I do not want to load). Those configuration files

So i need to keep all XML configuration files.

Secondly even if I do not use configuration file, I still cannot persist child's updated variable values. I have set up a simple pair of package (parent and child) to verify it. Inside parent package, i have a task to execute the child package... And i reconfigure child package and start to run parent package inside C#. I found that Child package's configuration updates are lost.

I have zipped all of my testing files and wish to send you all files via email if you are interest. I would definitely appreciate your help as I am stuck now.

Please help.

|||Right, but the configuration files override any changes you make with C# as John stated. How do you propose to get around that?|||

Steve Wang 2006 wrote:

Thank you... Your comments are valid suggestions.

However,

first, I have a master package (parent) and many child packages. Each individual child package has its own configuration file (childXXX.dtsConfig) which is an XML file. We did not use SQL as configuration. We want to reuse this entire package suite for two different business domains. In one scenario, we run packages using DTEXEC (or scheduled job) and it worked well. In another scenario, we want to RE-USE the entire package suite from C# code. I want to re-configure each packages inside C# code. And lauch the master package and HOPEFULLY execute all child packages with updated variable values (rather than reading from configuration files because configuration file contains old-values which I do not want to load). Those configuration files

So i need to keep all XML configuration files.

Secondly even if I do not use configuration file, I still cannot persist child's updated variable values. I have set up a simple pair of package (parent and child) to verify it. Inside parent package, i have a task to execute the child package... And i reconfigure child package and start to run parent package inside C#. I found that Child package's configuration updates are lost.

I have zipped all of my testing files and wish to send you all files via email if you are interest. I would definitely appreciate your help as I am stuck now.

Please help.

In your simple test, are you re-saving the child package after changing the variable settings? When you execute the parent from code, it will load the child package from the location pointed to by the package connection manager. It will not run the one you have loaded in your program.

I don't think you can do what you are trying to do. As Phil mentioned, the configurations will always take precedence over values saved in the package. If you want to run the same packages with different configurations, try using two different sets of configuration files, or have the parent package pass all values to the child packages through a parent package configuration. That way, you set the parent values when you execute it, and all the child packages pick up their values from it.

|||

Thanks Phil and John for prompt response...

In my simple test, I re-saved the re-configured parent and child packages to new XML files (using Application.SaveToXML(..) ) and reloaded them back with the hope of child package not loading configuration from configuration files. It did not work.... i.e. Child package still loaded variable values from child.dtsConfig file.

I have also tried to overwrite the configuration file using SSIS's API (i.e. Package.ExportConfigurationFile (....). This function seems to have some bugs that does NOTHING always.. Nothing is written out to file. Here is a posing that complains this API: http://sqljunkies.com/Forums/Search/default.aspx?SearchFor=1&SearchText=neetash

What I thought is: If i could overwrite the configuration file with updated variable setting (from C#), then the issue is solved. However the API "Package.ExportConfigurationFile(...)" is useless.

|||

Even if you were able to use the ExportConfigurationFile function - wouldn't that impact the ability to run the packages directly through DTEXEC? You'd effectively be altering both ways of running the packages.

If that is acceptable, I'd still suggest just loading the config file into an XML DOM object, change what you need to change, and save it back out.

If you need to keep the two methods of running the packages seperate, you'd still need to go with Parent Package configurations.

Parent / Child Package - Connections, Variables Etc.,

In respect to Parent / Child packages, can some one correct me if I'm wrong.

Even if connection managers are created in Parent package, the same needs to be created in child packages if they need to connect to database. On the other hand I can just create connection strings in variable in parent package(instead of connection managers itself) and use parent package variable configuration to configure the connection string of child package with the variable value.

Sorry If I'm confusing

The same with variables, the parent variable needs to be mapped to a child variable(using parent package variable config) to be used in child package, it cannot be used as it is.

Thanks

I think everything you've stated is correct.|||

Thanks for the confirmation Phil. Let me see how my experiment with Parent / Child packages goes.

Just a thought, It would be good to just specify the parent package in the child package and if it can pick up the variables, connections from the parent package.

Thanks

Parent - Multiple Child Package Execution

I have a parent package "A", I also have 4 child packages "B","B1","B2",B3"

In BIDS, I created a file connection in the package "A" to connect to the child packages. So whenever I want to run B1 or B2, I change the path in the file connection to point to B1 or B2 and so on. Since the developement environment is File system this works perfectly fine.

But in the Test / Production environment all packages are stored in the Sql server. How can I paramaterize the child package connection so that I can use configuration / variables to select which child package to execute.

Thanks

Set up four execute package tasks that point to each child package. Then use a variable precedence constraint to determine which execute package task to run. You could make the variable an integer and then use something like: @.ChildPackage == 1, @.ChildPackage == 2, etc...|||

One problem I see with this approach is that, If I develop another child package then I have to modify the master package to include this new child package and re-deploy. as the number of packages increases, it will be difficult to maintain. Basically I just want one execute package task which should be configurable ( say which package to execute or path of the package to execute)

Thanks

|||Could you use two Execute Package tasks, one configured for executing on the file system, the other for the database? Then use precedence constraints to control which one executes. So you'd pass in two variables - one being the connection to the package, the other setting a variable that controls which package executes.

Parent - Multiple Child Package Execution

I have a parent package "A", I also have 4 child packages "B","B1","B2",B3"

In BIDS, I created a file connection in the package "A" to connect to the child packages. So whenever I want to run B1 or B2, I change the path in the file connection to point to B1 or B2 and so on. Since the developement environment is File system this works perfectly fine.

But in the Test / Production environment all packages are stored in the Sql server. How can I paramaterize the child package connection so that I can use configuration / variables to select which child package to execute.

Thanks

Set up four execute package tasks that point to each child package. Then use a variable precedence constraint to determine which execute package task to run. You could make the variable an integer and then use something like: @.ChildPackage == 1, @.ChildPackage == 2, etc...|||

One problem I see with this approach is that, If I develop another child package then I have to modify the master package to include this new child package and re-deploy. as the number of packages increases, it will be difficult to maintain. Basically I just want one execute package task which should be configurable ( say which package to execute or path of the package to execute)

Thanks

|||Could you use two Execute Package tasks, one configured for executing on the file system, the other for the database? Then use precedence constraints to control which one executes. So you'd pass in two variables - one being the connection to the package, the other setting a variable that controls which package executes.

Parameters in data flow task with Oracle database source

In many DTS packages I have used parameterised queries for incremental loads from Oracle database sources using the Microsoft ODBC Driver for Oracle.

Now I want to migrate these packages to SSIS, but the OLE DB connection for Oracle does not support parameters.

I cannot use the "SQL command from variable" data access mode because of the 4000 character limitation on the length of string variables and expressions.

Am I missing an obvious workaround?

pshotts wrote:

In many DTS packages I have used parameterised queries for incremental loads from Oracle database sources using the Microsoft ODBC Driver for Oracle.

Now I want to migrate these packages to SSIS, but the OLE DB connection for Oracle does not support parameters.

I cannot use the "SQL command from variable" data access mode because of the 4000 character limitation on the length of string variables and expressions.

Am I missing an obvious workaround?

below is workaround, but i don't know how "obvious" it is:

Use property expressions. The SQL statement you use to query the Oracle source can be set by a variable, and that variable can be composed using a property expression such as: "select * from Orders where OrderID > " + @.LastOrderID. It's a parameterized query - and a very flexible one - in all but name.

|||Thanks Duane, but that does not get around the 4000 character limit on variable and expression length|||

Can you use a combination of query strings (in SSIS) and views in Oracle to reduce the amount of work you are doing in the one string?

Another alternative might be to execute a stored procedure that builds a temporary table (passing the parameters to the procedure), then read from the temporary table.

A third alternative would be to pull back the various discrete data sources from Oracle and handle joining/merging within SSIS. Can't say I'd recommend this one though. Better to just pull across the data you need from the remote source.

|||

Another option is to use a script source in which you concatenate the query string and call Oracle using OLEDB or ADO.Net in the script.

Donald

|||

Thanks Donald

That sounds like a good option. I understand that you mean we should use a Script Component as the Source in the Data Flow. The script will make the connection and build and run the required query.

Nice!

|||

That's correct.

You can still use a connection manager with your script component, to take advantage of that feature, but even that is not essential. although recommended.

Donald