sql-servervisual-studiosql-server-data-toolsdacpacsqlpackage

DACPAC and SQL Sequence

I have a Visual Studio database project (DACPAC) which includes a number of SQL Sequences. However when I deploy the DACPAC it always resets the sequence value to the default value included in the Create script (in this case 1). e.g.

CREATE SEQUENCE [dbo].[MySequence]
AS INT
START WITH 1
INCREMENT BY 1;

Can anyone suggest a way of either instructing the DACPAC to ignore the sequence start value, or some way of getting the DACPAC to restore the correct value as a post deployment step perhaps?

Thanks in advance

Solution

  • This is a known problem with sequences when using the SSDT tools. There are a couple of solutions.

    1. Ignore sequence objects when publishing.
    2. Use a custom deployment filter to ignore the starting value.
    3. Use sp_sequence_get_range instead of RESTART WITH to increment the counter after deploying to live.

    1. Ignore sequence objects when publishing

    This is the simplest option but the most awkward as it means you have to manually deploy sequences. Add the following to your publish profile

    <ExcludeSequences>True</ExcludeSequences>

    Or, from the command line

    /p:ExcludeObjectType=Sequences

    2. Use a custom deployment filter

    First download AgileSqlClub's deployment filter. Then add the following to your deployment profile:

    <AdditionalDeploymentContributors>AgileSqlClub.DeploymentFilterContributor</AdditionalDeploymentContributors>
<AdditionalDeploymentContributorArguments>SqlPackageFilter=IgnoreName(Order_No_Seq)</AdditionalDeploymentContributorArguments>

    Or, from the command line:

    /p:AdditionalDeploymentContributors=AgileSqlClub.DeploymentFilterContributor
/p:AdditionalDeploymentContributorArguments="SqlPackageFilter=IgnoreName(Order_No_Seq)"

    3. Use sp_sequence_get_range

    For this instead of using RESTART WITH on the production server to change the start value, use:

    DECLARE @range_first_value SQL_VARIANT;
EXEC sp_sequence_get_range
    @sequence_name = 'MySequence',
    @range_size = 1000,
    @range_first_value = @range_first_value OUTPUT;

    This way the start value will always match the expected value from the deployment script.

    Resources