March 2, 2017 at 7:47 am
I've been working with SSIS 2016 using a Parent-Child structure with a master package. I wanted to be able to pass an begin and end date from the master to the children and set them up as Project Parameters. When I tried to change the parameter value, it wouldn't let me. I learned from reading the project parameter values have to be known at the start of run time. So I set them up as Package variables in the master package. Then I set up variables in the child packages to receive the parent variable value. But this couldn't be done. I finally solved the issue (there's not much information out there and it's not really clear at least to me) by passing the variables to the child package parameters. In the child packages the parameters don't seem to be different from variables.
So I know why it works, but I don't understand WHY this makes sense. Parameters at the project level are fixed. But parameters at the package level function like variables. And to pass a variable to a child package, it needs to be passed to a parameter. Is this what others are finding, and if so, how does this make sense. Again, just trying to understand why, as this doesn't seem intuitive to me.
March 2, 2017 at 7:56 am
I'd be curious to hear more specifics about what issues you ran into. What type of setting were you using those params/variables for? Possibly a connection string?
Parameters, whether at the package or project level, are read-only during the execution of the package; their initial values are passed in at runtime but cannot be manipulated during the execution of the package. Variables, on the other hand, can be manipulated at runtime.
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
March 2, 2017 at 7:57 am
RonKyle - Thursday, March 2, 2017 7:47 AMI've been working with SSIS 2016 using a Parent-Child structure with a master package. I wanted to be able to pass an begin and end date from the master to the children and set them up as Project Parameters. When I tried to change the parameter value, it wouldn't let me. I learned from reading the project parameter values have to be known at the start of run time. So I set them up as Package variables in the master package. Then I set up variables in the child packages to receive the parent variable value. But this couldn't be done. I finally solved the issue (there's not much information out there and it's not really clear at least to me) by passing the variables to the child package parameters. In the child packages the parameters don't seem to be different from variables.So I know why it works, but I don't understand WHY this makes sense. Parameters at the project level are fixed. But parameters at the package level function like variables. And to pass a variable to a child package, it needs to be passed to a parameter. Is this what others are finding, and if so, how does this make sense. Again, just trying to understand why, as this doesn't seem intuitive to me.
This is not quite correct. Parameters do not change at execution time, regardless of whether they are declared at package or project level.
Variables in a master package can drive parameters in the child package, but those parameters' values are then fixed during execution in the child package.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 2, 2017 at 8:13 am
I'd be curious to hear more specifics about what issues you ran into. What type of setting were you using those params/variables for? Possibly a connection string?
Just trying to set a BeginDate and an EndDate for a warehouse ETL. I first did the ETL is 2005 and all the steps are in the same package. Having read your posts and Brian Knight's SSIS 2014 book, I'm re-doing the ETL in 2016 using parent-child (and in a couple of places--grandchild as I read your post that you do that and it doesn't create an issue) architecture. I'd first set up the dates as project parameters, but found I couldn't change the value.
Parameters, whether at the package or project level, are read-only during the execution of the package; their initial values are passed in at runtime but cannot be manipulated during the execution of the package. Variables, on the other hand, can be manipulated at runtime.
Variables in a master package can drive parameters in the child package, but those parameters' values are then fixed during execution in the child package.
You both have helped my understand WHY. I was thinking of the whole thing (parent and child packages as part of the overall project) as running at once. But it seems to me that I should have been thinking that the child packages have been dormant until activated. At activation time, the begin and end dates are known and are correctly passed in as parameters. Of course I don't try to change them, so that works. This is consistent with the way the project parameters work. But if I had a value that I would need to change during the life of the package, like I did for the dates in the master package, I'd have to create a variable. Which tells me the practical difference between the two.
Thanks, and Tim, I enjoy and learn a lot from your posts.
March 3, 2017 at 8:38 am
Thanks for the kind words! Glad to help.
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
March 3, 2017 at 8:47 am
You're welcome. I've also learned something else you both probably already knew. I've wondered why there was a required setting with the parameters. My thinking is "of course it's required, otherwise it wouldn't be there." I've discovered with the child packages that all required parameters have to be fed in from the parent. And I've learned just this morning that project parameters set to required have to be filled in when setting up a job step to reference that package.
I realize now that in a previous SSIS project I did I set up two variables when one of them should have been a parameter, because that value is fixed. And set it to not required, either, as there's no need for the value to be passed in. There's a lot of subtlety regarding parameters and variables. Very interesting stuff.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply