OFFSET Error

Question

Hi All,
I want to load a large data set in chunks and I followed the below link
https://www.sqlshack.com/sql-offset-fetch-feature-loading-large-volumes-of-data-using-limited-resources-with-ssis/
But in the DFT when I selected the "SQL Command from Variable" in the Data Access Mode , I am getting the below error
TITLE: Microsoft Visual Studio

------------------------------

Exception from HRESULT: 0xC0202009
Error at Data Flow Task [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E14 Description: "Invalid usage of the option NEXT in the FETCH statement.".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E14 Description: "Incorrect syntax near 'OFFSET'.".

SELECT *  
FROM [dbo].[Transactions]  
ORDER BY  [Transaction_Key]  
OFFSET 0  ROWS   
FETCH NEXT 10000  ROWS ONLY  

Please advice
Thanks

Answer 1

Hi @Amy Jandreau ,

The OFFSET FETCH functionality is available only starting from SQL 2012 onwards.
Since your SQL Server is 2008R2, it is not available.

You can easily switch your SQL Statement as follows:

SELECT TOP(10000) *  
FROM [dbo].[Transactions]  
ORDER BY  [Transaction_Key];  

UPDATE
For pagination you can try the following T-SQL statement:

SELECT * FROM  
(  
	SELECT *  
		, Seq = ROW_NUMBER() OVER (ORDER BY Transaction_Key)   
	FROM [dbo].[Transactions]  
	ORDER BY [Transaction_Key]  
) AS t  
WHERE Seq BETWEEN 101 AND 200;  

Answer 2

Hi @Amy Jandreau ,

Based on my test, the code could run successfully as a variable in SSIS package.

Please use the code in your SSMS query box to see if it will throw the error. Or try to use command in the source connector.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.