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.
Running this SSIS package results in an error. The error text is provided below.
[OLE DB Destination ] 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. ".
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“.
By making this small change, the SSIS package runs successfully, and data is also successfully inserted into the table.
- SEQUENCE object [MSDN link]
- OLE DB Destination – Table Fast Load options
Until we meet next time,
Be courteous. Drive responsibly.