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 1^st 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.

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.

Now, Create an Excel conection manager for destination.

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.

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

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.