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.

6_SQL_Server_Integration_Services_Sequentially_Inserting_rows_into_Excel_Spreadsheets

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

7_SQL_Server_Integration_Services_Sequentially_Inserting_rows_into_Excel_Spreadsheets

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.

8_SQL_Server_Integration_Services_Sequentially_Inserting_rows_into_Excel_Spreadsheets

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.

9_SQL_Server_Integration_Services_Sequentially_Inserting_rows_into_Excel_Spreadsheets

Connect the source output to data conversion transformation.

10_SQL_Server_Integration_Services_Sequentially_Inserting_rows_into_Excel_Spreadsheets

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

11_SQL_Server_Integration_Services_Sequentially_Inserting_rows_into_Excel_Spreadsheets

Edit the excel destination transformation as below.

12_SQL_Server_Integration_Services_Sequentially_Inserting_rows_into_Excel_Spreadsheets

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

13_SQL_Server_Integration_Services_Sequentially_Inserting_rows_into_Excel_Spreadsheets

   

14_SQL_Server_Integration_Services_Sequentially_Inserting_rows_into_Excel_Spreadsheets

Now edit the mappings as below.

15_SQL_Server_Integration_Services_Sequentially_Inserting_rows_into_Excel_Spreadsheets

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.

16_SQL_Server_Integration_Services_Sequentially_Inserting_rows_into_Excel_Spreadsheets

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.

17_SQL_Server_Integration_Services_Sequentially_Inserting_rows_into_Excel_Spreadsheets

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.

18_SQL_Server_Integration_Services_Sequentially_Inserting_rows_into_Excel_Spreadsheets

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

19_SQL_Server_Integration_Services_Sequentially_Inserting_rows_into_Excel_Spreadsheets

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.

20_SQL_Server_Integration_Services_Sequentially_Inserting_rows_into_Excel_Spreadsheets

21_SQL_Server_Integration_Services_Sequentially_Inserting_rows_into_Excel_Spreadsheets

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.

22_SQL_Server_Integration_Services_Sequentially_Inserting_rows_into_Excel_Spreadsheets

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.

23_SQL_Server_Integration_Services_Sequentially_Inserting_rows_into_Excel_Spreadsheets

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.

24_SQL_Server_Integration_Services_Sequentially_Inserting_rows_into_Excel_Spreadsheets

So, my final package looks like below.

25_SQL_Server_Integration_Services_Sequentially_Inserting_rows_into_Excel_Spreadsheets

 

So, if the package is supposed to create ‘N’ number of spreadsheets then the data load for 1st 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

   

About Mridul Chandhok

MSBI Developer having 4 years of experience in Report Writing using SSRS. Writing ETL packages for both OLTP and DW databases Using SSIS with focus on package optimization. In free time I love to explore new stuff on SQL Server. Love Bicycling and Swimming. Die Hard Fan of Heavy Metal Music.

View all posts by Mridul Chandhok →

Leave a Reply

Your email address will not be published.