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 Doesn't Recognize Child Settings

I have two issues:

I have a simple parent that uses an Execute Package Task to call a simple child package. The child package has a Data Flow that I disabled. When running the child package by itself, the data flow task is bypassed. When running the package via the parent the data flow task executes.

Second issue is when you disable the package configurations in the child, the parent doesn't recognize that it's diables and tries to load the configurations.

It's almost like there are some settings in a child that get ignored by the parent?

Anyone else experience this?

thx

My fault, pls ignore...

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