Scenario: Download Script
Few days back, I wrote a post
How to Export Data to Multiple Excel Sheets from Single SQL Server Table in SSIS Package
which was dividing the data to multiple sheets depending upon distinct value in one of the column.
In this post, we are going to split the rows without using any column value. Think about a situation where you have 3.5 million records and you would like to write 500,000 on each of the excel sheet.
The package should be able to take number of rows per sheet as variable value so we can change anytime we like.
Solution:
We will be using script task in SSIS Package to split large number of records from SQL Server table to Excel Sheets.
Step 1: Create an SSIS Package and Variables
Open SSDT ( SQL Server data tools) and then create an SSIS Package. Create variables as shown below.
ExcelFileName : Provide The name of file you would like to create.
FolderPath : Provide the folder path in which your excel file will be created.
RecordCntPerSheet : This is the variable that will decide that how many records you would like to write to each excel sheet. I set to 3, as I want 3 records per sheet.
TableName: Provide the table name from which you would like to read the records.
Create variables in SSIS Package to create multiple excel sheets after split records from SQL Server Table by using Script Task
Step 2: Create ADO.NET Connection in SSIS Package to use in Script Task
Step3: Add Variables to Script Task to use from SSIS Package
Bring the Script Task on Control Flow Pane in SSIS Package and open by double clicking Check-box in front of variable to add to Script Task.
Add variables to Script Task in SSIS Package to create multiple excel sheets from SQL Server Table
Step 4: Add Script to Script task Editor in SSIS Package to export data to multiple excel sheets from single table
Click Edit Button and it will open Script Task Editor.
Under #region Namespaces, I have added below code
using System.IO; using System.Data.OleDb; using System.Data.SqlClient;
Under public void Main() {
I have added below code.
string datetime = DateTime.Now.ToString("yyyyMMddHHmmss"); try { //Declare Variables string ExcelFileName = Dts.Variables["User::ExcelFileName"].Value.ToString(); string FolderPath = Dts.Variables["User::FolderPath"].Value.ToString(); string TableName = Dts.Variables["User::TableName"].Value.ToString(); Int32 RecordCntPerSheet =(Int32)Dts.Variables["User::RecordCntPerSheet"].Value; string RecordCntPerSheetDecimal = RecordCntPerSheet + ".0"; ExcelFileName = ExcelFileName + "_" + datetime; OleDbConnection Excel_OLE_Con = new OleDbConnection(); OleDbCommand Excel_OLE_Cmd = new OleDbCommand(); //Construct ConnectionString for Excel string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + FolderPath + ExcelFileName + ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;\""; //drop Excel file if exists File.Delete(FolderPath + "\\" + ExcelFileName + ".xlsx"); //USE ADO.NET Connection from SSIS Package to get data from table SqlConnection myADONETConnection = new SqlConnection(); myADONETConnection = (SqlConnection)(Dts.Connections["DBConn"].AcquireConnection(Dts.Transaction) as SqlConnection); //Read distinct Group Values for each Excel Sheet string query = "select ceiling(count(*)/"+RecordCntPerSheetDecimal+") AS LoopCnt from " + TableName; decimal LoopCnt = 0; //Get the Count of Sheets need to be created SqlCommand cmd = myADONETConnection.CreateCommand(); cmd.CommandText = query; LoopCnt = (decimal)cmd.ExecuteScalar(); string SheetName = "Sheet"; int startRowCnt=0; int endRowCnt = RecordCntPerSheet; for (int sheetloop=1; sheetloop <= LoopCnt; sheetloop++) { //Load Data into DataTable from SQL ServerTable string queryString =";with cte as (Select *, Row_Number() over (order by (Select 1)) AS RowNumber from "+ TableName+") Select * From cte where RowNumber > "+startRowCnt.ToString()+" and RowNumber<="+endRowCnt.ToString(); SqlDataAdapter adapter = new SqlDataAdapter(queryString, myADONETConnection); DataSet ds = new DataSet(); adapter.Fill(ds); //Get Header Columns string TableColumns = ""; // Get the Column List from Data Table so can create Excel Sheet with Header foreach (DataTable table in ds.Tables) { foreach (DataColumn column in table.Columns) { TableColumns += column + "],["; } } // Replace most right comma from Columnlist TableColumns = ("[" + TableColumns.Replace(",", " Text,").TrimEnd(',')); TableColumns = TableColumns.Remove(TableColumns.Length - 2); //MessageBox.Show(TableColumns); //Use OLE DB Connection and Create Excel Sheet Excel_OLE_Con.ConnectionString = connstring; Excel_OLE_Con.Open(); Excel_OLE_Cmd.Connection = Excel_OLE_Con; Excel_OLE_Cmd.CommandText = "Create table [" + SheetName +sheetloop.ToString()+ "] (" + TableColumns + ")"; Excel_OLE_Cmd.ExecuteNonQuery(); //Write Data to Excel Sheet from DataTable dynamically foreach (DataTable table in ds.Tables) { String sqlCommandInsert = ""; String sqlCommandValue = ""; foreach (DataColumn dataColumn in table.Columns) { sqlCommandValue += dataColumn + "],["; } sqlCommandValue = "[" + sqlCommandValue.TrimEnd(','); sqlCommandValue = sqlCommandValue.Remove(sqlCommandValue.Length - 2); sqlCommandInsert = "INSERT into [" + SheetName+sheetloop.ToString() + "] (" + sqlCommandValue + ") VALUES("; int columnCount = table.Columns.Count; foreach (DataRow row in table.Rows) { string columnvalues = ""; for (int i = 0; i < columnCount; i++) { int index = table.Rows.IndexOf(row); columnvalues += "'" + table.Rows[index].ItemArray[i] + "',"; } columnvalues = columnvalues.TrimEnd(','); var command = sqlCommandInsert + columnvalues + ")"; Excel_OLE_Cmd.CommandText = command; Excel_OLE_Cmd.ExecuteNonQuery(); } } startRowCnt += RecordCntPerSheet; endRowCnt += RecordCntPerSheet; Excel_OLE_Con.Close(); } Dts.TaskResult = (int)ScriptResults.Success; } catch (Exception exception) { // Create Log File for Errors using (StreamWriter sw = File.CreateText(Dts.Variables["User::FolderPath"].Value.ToString() + "\\" + "ErrorLog_" + datetime + ".log")) { sw.WriteLine(exception.ToString()); Dts.TaskResult = (int)ScriptResults.Failure; } }
Step 5: Save the Script and Run your SSIS Package to load data to multiple Excel Sheets from SQL Server Table by splitting depending upon number of rows
Save the script and close the windows. Your SSIS Package is read to read data from SQL Server table and will split to multiple sheets depending upon the total number of records and RecordsCntPerSheet variable value.
I execute my package after setting the RecordsCntPerSheet value=3. I had total 11 records in my table. It create an excel file with 4 sheets as can be seen below. First three sheets will have three records and 4th will have only 2 records.
How to split large table data to multiple excel sheets by using SSIS Package- Script Task
Check out video demo how to split large data into multiple excel sheets on single excel file.
Check out our related posts/videos on Excel Source and Destinations (Script Task- Dynamic)
- How to Load Data from Excel Files when Number of Columns can decrease or order is changed in Excel Sheet
- How to Load Only Matching Column Data to SQL Server Table from Multiple Excel Files (Single Sheet per file) Dynamically in SSIS Package
- How to Load Excel File Names with Sheet Names ,Row Count,Last Modified Date, File Size in SQL Server Table
- How to Load Multiple Excel Files with Multiple Sheets to Single SQL Server Table by using SSIS Package
- How to Load Matching Sheets from Excel to Table and Log Not Matching Sheets Information in SQL Server Table
- How to create Table for each sheet in Excel Files and load data to it dynamically in SSIS Package
- How to Create Table per Excel File and Load all Sheets Data Dynamically in SSIS Package by using Script Task
- How to create CSV file per Excel File and Load All Sheets from Excel File to it in SSIS Package
- How to Create CSV File for Each Excel Sheet from Excel Files in SSIS Package
- How to Load Excel File Name and Sheet Name with Data to SQL Server in SSIS Package
- How to Import data from Multiple Excel Sheets with a pattern of sheet names from Multiple Excel File in SSIS Package
- How to import Data from Excel Files for specific Sheet Name to SQL Server Table in SSIS Package
- Load Data To Tables according to Excel Sheet Names from Excel Files dynamically in SSIS Package
- How to Load Excel Files with Single/ Multiple Sheets to SQL Server Tables according to Excel File Name Dynamically
- How to Read Excel Sheet Data after Skipping Rows in SSIS Package by using Script Task
- How to read data from Excel Sheet and Load to Multiple Tables by using Script Task in SSIS Package
- How to create Excel File Dynamically from SQL server Table/View by using Script Task in SSIS Package
- How to create Excel File Dynamically for Stored Procedure Results in SSIS Package by using Script Task
- How to Export SQL Server Tables from Database to Excel File Dynamically in SSIS Package by using Script Task
- How to Convert CSV/Text Files to Excel Files in SSIS Package by using Script Task
- How to Load All CSV Files to Excel Sheets ( Sheet Per CSV) in single Excel File in SSIS Package
- How to Load All CSV Files to Single Excel Sheet with File Names in an Excel File Dynamically in SSIS Package
- How to Create Sample Excel file with Sheet from each table with Top 1000 Rows per sheet in SSIS Package
- How to Export Data to Multiple Excel Sheets from Single SQL Server Table in SSIS Package
- How to split large table data into multiple Excel Sheets on Single Excel File by using SSIS Package
- How to Export All tables of a database to Excel Files with Date-time in SSIS Package
- How to read Cell Value from Excel by using Script Task in SSIS Package
There is a small flaw here, columnvalues += "'" + table.Rows[index].ItemArray[i] + "',";
ReplyDeleteif the row has a value like Oscar'Adam , like O'Neal etc. then this will choke n fail.. I tried to apply replace , for example s.Replace("'", @"\'"); .. but this is array and am not very good in c#, this is an issue...
any update will be helpful
ReplyDeleteHi,
ReplyDeleteInstead of loading large table data into multiple Excel sheets, how can we load large table data into multiple Excel files.
Hi, how can we load large table data into multiple Excel files by column values?
ReplyDelete