Pass SSIS Object to child SSIS package

Question

Pass SSIS Object to child SSIS package

blasto_max

SSCertifiable

Points: 5025
More actions
December 18, 2013 at 3:09 pm

#280461

I tried to pass a SSIS Object (actually a DataSet) to a child package. I got an error which tells me that I am not allowed to do that. Why can't I do it ? This never happens in a programming language. Are there any workarounds for this ?
Thanks.
EDIT -
Error - Property "Value" with type "Object" of variable "User::MY_DATA_SET"
can not be exported to the configuration file.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

You were trying to pass an object variable with a dataset in it to the child package, or something else? Need a little more detail about exactly what you're trying to pass.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

blasto_max SSCertifiable Points: 5025 More actions · Answer 2

Evil Kraig F (12/18/2013)
You were trying to pass an object variable with a dataset in it to the child package, or something else? Need a little more detail about exactly what you're trying to pass.

Actually, it is an Object which is meant to hold a DataSet. I was trying to pass this variable via child package config. Does that make my question clear ?

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 3

I didn't think you could pass an object between, virtually everything else yes.. But not an object.

CEWII

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

blasto_max (12/18/2013)
Evil Kraig F (12/18/2013)
You were trying to pass an object variable with a dataset in it to the child package, or something else? Need a little more detail about exactly what you're trying to pass.
Actually, it is an Object which is meant to hold a DataSet. I was trying to pass this variable via child package config. Does that make my question clear ?

Yup, just wanted to make sure we were talking apples to apples.

Check out this article. System.Object doesn't want to work via configurations.

http://agilebi.com/jwelch/2009/10/03/passing-an-object-from-a-parent-package-to-a-child[/url]

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

blasto_max SSCertifiable Points: 5025 More actions · Answer 5

Evil Kraig F (12/18/2013)
blasto_max (12/18/2013)
Evil Kraig F (12/18/2013)
You were trying to pass an object variable with a dataset in it to the child package, or something else? Need a little more detail about exactly what you're trying to pass.
Actually, it is an Object which is meant to hold a DataSet. I was trying to pass this variable via child package config. Does that make my question clear ?
Yup, just wanted to make sure we were talking apples to apples.
Check out this article. System.Object doesn't want to work via configurations.
http://agilebi.com/jwelch/2009/10/03/passing-an-object-from-a-parent-package-to-a-child[/url]

Thank you very much. It works now ! But, this is so unintuitive. I wish it was more like programming.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 6

blasto_max (12/18/2013)
Thank you very much. It works now ! But, this is so unintuitive. I wish it was more like programming.

My pleasure. I'm pretty sure the problem comes down to the unknown size of the variable involved and the child package doesn't receive anything ByRef, only ByValue. The object in question should be passed ByRef in my mind, but they don't. No idea why. I didn't see anything in my browse showing this was improved in 2012 or beyond, either.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 7

Nice trick. It's also another notch on a 2x4 list of reasons why I try to do everything in T-SQL. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 8

Jeff Moden (12/18/2013)
Nice trick. It's also another notch on a 2x4 list of reasons why I try to do everything in T-SQL. 😉

Yeah, yeah, my favorite Luddite. 😛

Considering I avoid child packages in general like the plague, I don't run into this very often. I find them to offer little assistance, as you can rarely modularize a child package well enough for multiple inheritance of over 3 packages (my minimum for when I look to standardize code calls in just about everything). SSIS shouldn't be treated like standard coding where you're typically trying to pass information between multiple component functions off a MAIN() or whatever.

SSIS however is awesome for laying out processing methodology and calling sequential procedures with obvious and self-documenting logic pathing, however. There's plenty of other things it can do but I find a single package controlling a complete task to be very easy to read without having to dig through code to find the control pass points or dig through signficantly complex if trees. There's also places where it's faster than T-SQL (small lookup lists come to mind) if your transforming across servers.

And now I sound like a brochure. No more after midnight typing for me. Bedtime! :hehe:

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

blasto_max SSCertifiable Points: 5025 More actions · Answer 9

Thanks for all the tips @Evil Kraig F. I made a child package which will accept a DataSet from a parent package and convert it into a CSV file. Since you mentioned that child packages are inconvenient and are to be avoided, I was wondering if you can see/foresee any problems in my approach ?

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 10

blasto_max (12/19/2013)
Thanks for all the tips @Evil Kraig F. I made a child package which will accept a DataSet from a parent package and convert it into a CSV file. Since you mentioned that child packages are inconvenient and are to be avoided, I was wondering if you can see/foresee any problems in my approach ?

Since you asked... 🙂

You can't change the metadata of a target CSV Connection at runtime, at least not without third party tools or completely rolling your own script component to do this. So, while this child package will work fine in this particular instance, it's not a re-usable child for another process unless the output CSV is exactly the same. This comes back to the concern of over-coding a solution, at least to me. What benefit is the child package vs. just having it contained within a single parent package?

When I work with SSIS, I typically look at logical breakpoints, not code breakpoints, as to when to move to a new SSIS package. This has more to do with job restarts due to network breakdowns (or other random issues) at a particular step in a series than for code encapsulation.

If you're looking to encapsulate a step series for testing runs (For example, a truncate-load on a particular table without running an entire package) the sequence container allows not only for collapsable organization of multiple code pieces, but rt-clicking on it allows you to execute a container for testing. Very useful.

My general concern with Child Packages is not that they can be fussy to work with, it's that they segregate sections of a process usually with little purpose. As an example, you're called at 2 AM by your DBAs (you're a dev) and your package broke. They send you a lovely .log file and the DTSX package from production (because never trust dev still looks like that) and you crack it open and get to work. You eventually find out the problem is in the child package and now you have to track down the guy on call (hopefully he's not asleep again) and get yet another package offloaded and shipped to you. End of the world? Absolutely not. Annoyance with little to no gain? Usually.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 11

Evil Kraig F (12/19/2013)
SSIS however is awesome for laying out processing methodology and calling sequential procedures with obvious and self-documenting logic pathing, however. There's plenty of other things it can do but I find a single package controlling a complete task to be very easy to read without having to dig through code to find the control pass points or dig through signficantly complex if trees.

Now THAT I agree with. It's also a heck of a lot easier to run things "in parallel" than it is with T-SQL.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)