May 11, 2017 at 2:52 am
I'm in the process of upgrading our data warehouse load package from 2008R2 to 2016. I've looked at setting the connection managers dynamically using a Parent Package variable so I only need to change this in one place when it comes to roll out the package to the new servers and this is working fine. Just for badness I decided to see if I could get the name of the connection manager to change dynamically using a variable. This worked perfectly. The thing is I'm now seeing that the child package has failed when I run it in SSDT. Each of the individual tasks in the child package runs successfully and I can see data being inserted in the appropriate places but I'm still seeing a big red cross on the child package on the parent package's Control Flow. I've set Delay Validation to TRUE everywhere to stop it failing out of the blocks but I'm at a bit of loss to see why it's showing as failed when everything's running.
I can solve it easily enough by tweaking the Maximum Error Count but I'm dubious about this because if there's an error I'm not expecting then it will be allowed through. I can also not set the name dynamically, I don't like this idea because I'll be rolling this package out over four servers and there'll be a lot of manual work.
Any suggestions?
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
May 11, 2017 at 7:14 am
Neil Burton - Thursday, May 11, 2017 2:52 AMI'm in the process of upgrading our data warehouse load package from 2008R2 to 2016. I've looked at setting the connection managers dynamically using a Parent Package variable so I only need to change this in one place when it comes to roll out the package to the new servers and this is working fine. Just for badness I decided to see if I could get the name of the connection manager to change dynamically using a variable. This worked perfectly. The thing is I'm now seeing that the child package has failed when I run it in SSDT. Each of the individual tasks in the child package runs successfully and I can see data being inserted in the appropriate places but I'm still seeing a big red cross on the child package on the parent package's Control Flow. I've set Delay Validation to TRUE everywhere to stop it failing out of the blocks but I'm at a bit of loss to see why it's showing as failed when everything's running.I can solve it easily enough by tweaking the Maximum Error Count but I'm dubious about this because if there's an error I'm not expecting then it will be allowed through. I can also not set the name dynamically, I don't like this idea because I'll be rolling this package out over four servers and there'll be a lot of manual work.
Any suggestions?
If you are using the project deployment model, you can share a single connection between all packages in a project. The properties of this connection can be configured through use of appropriate environment variables. That's not a lot of work over four servers.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 11, 2017 at 7:34 am
Phil Parkin - Thursday, May 11, 2017 7:14 AMIf you are using the project deployment model, you can share a single connection between all packages in a project. The properties of this connection can be configured through use of appropriate environment variables. That's not a lot of work over four servers.
I know there's several ways of flaying this particular feline and none of them are going to be huge amounts of work. At the moment I'm just trying to establish the best way to do it to minimise the risk of making a mistake somewhere. The project deployment model is definitely one approach I'm going to consider. Project level connections are likely to be very useful things.
The question was more about why I was getting a package failure notification when all the individual parts ran successfully. Of course now it's stopped telling me it's failing and I swear I've not done anything I've not done before!
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply