Use SSIS to load data in chunks from SQLServer to Excel

This sample SSIS package has below steps:

1. Read row count of the data to load from SQL Server and save the row count in variable

2. Split the rows to n chunks in a For Loop Container, create an Excel sheet for each chunk and load data into it

Package Control Flow:

1. Create a Execute SQL Task to read row count from SQL Server

2. Create a For Loop Container to split the row count to n chunks 

3. Create a Execute SQL Task in For Loop Container to create Excel sheet for each chunk

4. Create a Data Flow Task In For Loop Container to load chunk data to Excel sheet

Below document describe the details for each component

Variables in package:

Create variables in package as below

Execute SQL Task – Read Row Count

Set SQLStatement: select count(*) as count from dbo.int

Map the result of count to variable User::rowcount in Result Set page

For Loop Container:

Set InitiExpression: @iterator = 0

Set EvalExpression: @iterator*@@chunksize < @rowcount

Set AssignExpression: @iterator = @iterator + 1

Execute SQL Task - Create Excel Sheet

Select Excel in ConnectionType

Set SQLStatement in Expressions: "CREATE TABLE `" + @[User::sheetName] + "` ( `column1` INTEGER)"

Data Flow Task - Load Data To Excel

Create ADO NET Source component to load data from SQL Server

Create Excel Destination component to load data to Excel table

Set the DelayValidation property of this data flow task to "True" to bypass the validation before execution

In Expressions of this data flow task, set [ADO NET Source].[SqlCommand] :

"select * from dbo.int order by a OFFSET " + (DT_WSTR, 10) (@[User::iterator] * @[User::chunksize]) + " ROWS FETCH NEXT " + (DT_WSTR, 10) @[User::chunksize] + " ROWS ONLY"

In Excel Destination, create a Excel connection manager for target Excel file, set the table name with variable User:excelTableName

Execute this package will dynamically create sheets in the Excel file. It will fail to execute if the sheets already exists. You can update the expression to include TimeStamp in the Excel sheet name to create new sheets for each execution.