How to Create Sample Excel file with Sheet from each table with Top 1000 Rows per sheet in SSIS Package - SSIS Tutorial

Scenario: Download Script

You are working as an ETL Developer / SSIS developer. You are asked to create an Excel file with date time on each execution and load the top X rows from each table to new excel sheet to newly created Excel File. There should be new Excel Sheet for each Table from Database.

The excel file can be used for data analysis or test purpose.

Log File Information : In case your SSIS Package fail. Log file will be created in the same folder where your Excel File will be created.The log file name will be ExcelFileName_datetime.log.

Solution:

We are going to use Script Task in SSIS Package to dump data from SQL Server Database tables to Excel file. Let's create variables so we can change the Top X anytime we like, also the location where the file should be created and name of the file.

FolderPath: Where you would like to create an Excel File

ExcelFileName: The name of Excel file you would like to create

TopRowCount : Provide the values for Top clause, I am using Top 1 from each table

How to Export top X rows from each table to Excel File Dynamically in SSIS Package

Step 2: Create ADO.NET Connection in SSIS Package to use in Script Task

Create ADO.NET Connection Manager so we can use in Script Task to get tables data to export to Excel. This ADO.Net connection should be pointing to Database that we want to export to an Excel File.

Create ADO.NET Connection in SSIS Package to use in Script Task to Export All Tables from Database to Excel File with Top X Rows

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.

How to Export Top X Rows from each Table to Excel File to create sample data in SSIS Package

Step 4: Add Script to Script task Editor in SSIS Package to Export Top X Rows from  All Tables from SQL Server Database to Excel File

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 TopRowCount = Dts.Variables["User::TopRowCount"].Value.ToString();
                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 list of Tables with Schema from Database
                string query = "SELECT Schema_name(schema_id) AS SchemaName,name AS TableName FROM   sys.tables WHERE  is_ms_shipped = 0";

                //MessageBox.Show(query.ToString());
                SqlCommand cmd = new SqlCommand(query, myADONETConnection);
                //myADONETConnection.Open();
                DataTable dt = new DataTable();
                dt.Load(cmd.ExecuteReader());
                myADONETConnection.Close();

                //Loop through datatable(dt) that has schema and table names

                foreach (DataRow dt_row in dt.Rows)
                {
                    string SchemaName = "";
                    string TableName = "";
                    object[] array = dt_row.ItemArray;
                    SchemaName = array[0].ToString();
                    TableName = array[1].ToString();

                    //Load Data into DataTable from SQL ServerTable
                    // Assumes that connection is a valid SqlConnection object.
                    string queryString =
                      "SELECT top "+TopRowCount+" * from " + SchemaName + "." + TableName;
                    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 [" + SchemaName+"_"+TableName + "] (" + 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 [" + SchemaName+"_"+TableName + "] (" + 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();
                        }

                    }
                    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() + "\\" +
                    Dts.Variables["User::ExcelFileName"].Value.ToString() + datetime + ".log"))
                {
                    sw.WriteLine(exception.ToString());
                    Dts.TaskResult = (int)ScriptResults.Failure;
                    
                }

               }

Step 5: 

Save the script in Script Task Editor and then run your SSIS Package to export top X rows from each of the table to new Excel Sheet in Excel File.

I ran my SSIS Package with Top 1 and here is my output Excel file.

Create Sample Excel file with Top X rows from each table in a database by using SSIS Package