5

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.%'?

Improve this question

1 Answer 1

Reset to default
7

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.

Improve this answer
1
  • 2
    I approve of the general approach but I think you're going to have a much more painful experience with using @@server. Way too much text and permutations therein to deal with. I think they'd be much better served by retrieving the value as the OP originally thought of SELECT SERVERPROPERTY('productversion') AS ProductVersion and then apply your logic via precedence constraints
    – billinkc
    Commented Dec 28, 2015 at 21:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.