SQL Server Integration Services – Sequentially Inserting rows into Excel Spreadsheets from sql server table

Hi Friends,

Here’s an interesting scenario on SQL Server Integration Services.

How to Sequentialy Insert specific number of rows into excel spreadsheet and dynamically generate a new spreadsheet for next set of sequential insert?

Suppose, I have 10000 records in my table and i want to export  a specific number of rows, let’s say 900 records in excel ‘s 1st spreadsheet and then next 900 to next spreadsheet and so on. In this process, the spreadsheets wil be created dynamically on the basis of row count in the source table.

My Table has an Identity column and the data looks something like below.

1_SQL_Server_Integration_Services_Sequentially_Inserting_rows_into_Excel_Spreadsheets

Here, In the table Column “Id” is of type “bigint” and “Name” is of type “Char(1)”.

So, lets start by creating an OLE DB connection to sql server as Source.

2_SQL_Server_Integration_Services_Sequentially_Inserting_rows_into_Excel_Spreadsheets

Now, Create an Excel conection manager for destination.

3_SQL_Server_Integration_Services_Sequentially_Inserting_rows_into_Excel_Spreadsheets

Now, In control flow of SSIS drag and drop an Execute SQL task and type the below query in the task.

Select Cast(CEILING(COUNT(1)/900.00) as int) As TabCount 
from chkrows

The query determines the number of spreadsheets to be created.

4_SQL_Server_Integration_Services_Sequentially_Inserting_rows_into_Excel_Spreadsheets

Now create a vaiable named “TabCount” of type “Int32” and pass the output of query to the variable.

5_SQL_Server_Integration_Services_Sequentially_Inserting_rows_into_Excel_Spreadsheets

Create another variable “Counter” of type “Int32”.

Now, drag and drop a for loop container in the control flow, connect it after the Execute SQL Task  and configure as below.