Use SSIS to load data in chunks 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.