Denali | SQLTwins by Nakul Vachhrajani

Traditionally, sequential numbers in a database system were managed by the use of IDENTITY columns. SQL Server 2012 saw the advent of the SEQUENCE object, which can be used to generate a sequence of numeric values according to a user-defined specification. Recently, I was working on an SSIS package which used the OLE DB transformation to load data into a table where one of the columns was populated by the SEQUENCE object. During the development of this package, I ran into an interesting issue.

Standard load options of the SSIS OLE DB destination throw an error when inserting data into a table where SEQUENCE is used.

The workaround is quite simple – the OLE DB fast load options. Allow me to walk through a sample demonstrating this issue and the workaround.

Assume that we have an extremely basic sample SEQUENCE object which starts at 1 and increments by 1. This SEQUENCE is then used as a default value for one of the columns in our test table.

USE [tempdb];
GO
--Safety  Check
IF OBJECT_ID('df_RecordId','D') IS NOT NULL
BEGIN
    ALTER TABLE dbo.SequenceTest 
        DROP CONSTRAINT df_RecordId;
END
GO

IF OBJECT_ID('dbo.RecordIdSequence','SO')  IS NOT NULL
BEGIN
    DROP SEQUENCE dbo.RecordIdSequence;
END
GO

IF OBJECT_ID('dbo.SequenceTest','U') IS NOT NULL
BEGIN
    DROP TABLE dbo.SequenceTest;
END
GO

--Create the sequence first
CREATE SEQUENCE dbo.RecordIdSequence
    START WITH  1
    INCREMENT BY 1;
GO

--Use this SEQUENCE on a default constraint
--associated with one of the columns in the table
CREATE TABLE dbo.SequenceTest 
    (RecordId INT NOT NULL 
              CONSTRAINT df_RecordId DEFAULT
              NEXT VALUE FOR dbo.RecordIdSequence,
     RowValue VARCHAR(50)
    );
GO

My SSIS package has nothing else other than a simple Data-Flow Transformation with 1 (one) OLE DB source and 1 (one) OLE DB destination.

The OLE DB source uses the following query to generate the test data.

SELECT t.RowValue
FROM (VALUES ('One'),
             ('Two'),
             ('Three'),
             ('Four')
     ) AS t (RowValue)

The OLE DB destination uses the standard (non fast-load) configuration as shown in the screenshot below.

Showing use of the SSIS OLE DB destination standard table load configuration

OLE DB standard table load configuration

Running this SSIS package results in an error. The error text is provided below.

[OLE DB Destination [2]] Error: SSIS Error Code DTS_E_OLEDBERROR. 
An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. 
Source: "Microsoft SQL Server Native Client 11.0" 
Hresult: 0x80004005 
Description: 
"NEXT VALUE FOR function cannot be used if ROWCOUNT option has been set, 
or the query contains TOP or OFFSET. ".

SSIS table load error when using OLE DB standard load into a table with the SEQUENCE object

To resolve the error, all we need to do is to change the OLE DB destination to use fast load options, by choosing “Table or View – fast load” as the “Data access mode“.