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.

Now, Create a variable named “TabCreate” of type “String”. In the expression for the variable type the create table statement as below.

The spreadsheets to be created dynamically will be named in the format “ExcelDest_”@User::Counter.

Now, Drag and drop an Execute SQL Task inside the for loop container and edit it as below.

Note that the control has “SQLSourceType” set to “Variable” and “SourceVariable” is set to “TabCreate”.

Now, Drag and drop a data flow task inside the for loop container and edit it.

Drag and drop an OLE DB source and edit it to extract the rows from the table you want to export to excel.

Connect the source output to data conversion transformation.

Now, create a variable named “TabName” of type “String” and edit its expression as below.

Edit the excel destination transformation as below.

Rename the Table as “ExcelDest_0” and remove the column “Copy of Name” as below.

Now edit the mappings as below.

Click OK.

Now, edit the excel destination transformation and change the Data Access Mode to “Table name or view name variable”  and give the variable name as “TabName” as below.

Hence, the spreadsheet will be created dynamically in the Execute SQL task and will be used as destination table passed as Variable “TabName” here.

Now, Depending upon the row count of the table, If I have TabCount >1 then in that case it would be required to check the maximum ID inserted in the previous spreadsheet.

So, we create another variable named “MaxIDQuery” of type “String” and edit its expression as below.

Hence, one more Execute SQL Task is required to check the Maximum ID for previous spreadsheet and for the first spreadsheet we can simply load data in excel’s first spreadsheet after the creation of first spreadsheet.

Now, We create another variable named “MaxId” of type “Double” which will store the maximum ID from previous Spreadsheet.

So, instead of using table/query for OLE DB source in Data flow a SQL Command from vaiable is passed to check if its for the first spreadsheet or for any number of spreadsheet created after first.

So, Lets create another variable named “FetchData” of type “String” and edit its expression to extract data to be inserted into first spreadsheet and for any number of shpreadsheets after the first.

Now, In the for loop container in Control flow, Connect the Data flow task to the “Create excel spreadsheet”  Execute SQL Task and edit the precedence constraint to contain the expression.

Now, connect the “Max Id from Previous Spreadsheet” Execute SQL Task to the “Create excel spreadsheet”  Execute SQL Task and edit the precedence constraint to contain the expression.

Now, connect the the Data flow task to the “Max Id from Previous Spreadsheet” Execute SQL Task and edit the precedence constraint and check the “Logical OR, one constraint must evaluate to True“ option.

So, my final package looks like below.

So, if the package is supposed to create ‘N’ number of spreadsheets then the data load for 1^st spreadsheet,

the control will flow from Step1 ->Step3 and

for subsequent data loads the control will flow from Step1-Step2-Step3.

Happy Learning!!!

Regards

Mridul Chandhok

Like us on FaceBook  |  Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook