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 issue?

I have noticed an issue with parent package variables. I have a package with multiple parent package variables defined, call them X, Y, and Z. I also have a parent package that calls this other package. The parent package has variable definitions for X and Z. It seems that the value for X will be passed along, and Y will give a warning since there is no variable of that name in the parent. The issue is that Z will not be passed along. It seems like the parent package configuration process stops after it encounters one missing variable.

Is this a know issue? Is it by design?Yep, I see the same thing.|||Good to know I'm not crazy! Thanks.|||Look for a fix in SP3, perhaps.

There is already a bug filed for this issue.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=174510

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 / 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

Paramter Passing

Is it possible to pass variables/parameters to a report other thank through
a url using ASP, as this will compromise our security ?
Thanks in advanceTwo options. 1. Use web services. 2. Pull the sensitive parameters by using
the global user!userid. This value tells you who is running the report and
you can then use that to query the database to find out the sensitive
parameters. You might need to do something like having a table with the
username, report and sensitive parameters that your write to before your
application calls the report. And, as I said, you can also use web services.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Con" <conh@.melbournehosting.com> wrote in message
news:u8z7eiPvEHA.3276@.TK2MSFTNGP15.phx.gbl...
> Is it possible to pass variables/parameters to a report other thank
through
> a url using ASP, as this will compromise our security ?
> Thanks in advance
>

parameters in sql server 2000

Hi!

I need how to pass variables struct witt n fields and n records
to procedures in transac sql in one bbdd sql server 2000,
this parameters are in/out.

Thanks.Sounds like an array:

http://www.sommarskog.se/arrays-in-sql.html

--
David Portas
SQL Server MVP
--