SSIS Package – How to Restrict Target DB Instance Version

sql serversql-server-2012ssisssis-2012

How can I restrict an SSIS Package to only execute on SQL Server 2012?

Is there a built-in way to do this (metadata?), or do I need to do a query?

(Possibly belongs in a new question):

I can get the version using SERVERPROPERTY('ProductVersion'), but how do I verify that it is like '11.%'?

Best Answer

Create an initial Execute SQL Task in the Control Flow and use the following code in it:

IF ((SELECT CAST(SERVERPROPERTY('ProductVersion') as CHAR(2))) = 11)
    BEGIN
        SELECT 'TRUE' As Result
    END
    ELSE
        SELECT 'FALSE' As Result

You can of course do several things with this, one of the easiest and clearest for SSIS developers would be to store the results in a SSIS variable and assign the Precedent Constraint after the SQL Task to Expression and Constraint.

If the Execute SQL Task Succeeds and returns TRUE it will allow the rest of the execution, which could be a single package, part of a larger execution stream, or a master package. If it fails or returns FALSE your package stops... might be worth putting in another path for an alert message or log of this though.

You can also use (SELECT @@VERSION) LIKE 'Microsoft SQL Server 2014%'. I like this as it's a more familiar version nomenclature.

Related Question