Executing Packages Based on Version RRS feed

  • Question

  • Hi Everyone!

    I have a design problem that I am not too sure how to solve yet although I have an idea of how to do it.

    Basically I have master package that does some things and needs to call certain child packages based on what release version the SQL Server instance is on.

    The master package will gather all the different instances from a global environment, then loop through them and execute the child package for each server instance.

    So for example the master will run and gather the following:

    Instance Name Version #

    Dev1 1.2

    Dev2 1.4

    .. and so on.

    Once the main package is looping through the instances, for the Dev1 instance name it needs to execute the package which is stamped with the 1.2 version. The way I was thinking of achieving this is to utilize the "VersionMajor" property of each child package and stamp it with the proper version. Keep in mind however, that the exact version # might not necessarily exist as a child package. In cases like that I would execute the top closest version number. So for example, if the instance version was set to 1.6 and we do not have a 1.6 version package, then the 1.4 version would be executed.

    I am looking for some examples on how I can loop through all the packages in a specific folder and find their version numbers. If I can identify the package name or the ID, then I think I would be able to execute it. 

    Of course, I am also open to suggestion if someone has a better idea.

    Keep in mind the # of child packages would be dynamic. Today it could be 2, tomorrow it could be 10. So I want the solution to be dynamic and be able to handle that without having to update the master package. The solution outlined in this example would not work for me since the "conditions" are hard coded inside a package and would need to be updated each time there is a new version.

    Tuesday, March 31, 2015 1:09 PM

All replies