Create an excel file and extract the sql server table data in this file using Script task in SSIS

We have a requirement that we need to extract the SQL server table records into the excel sheet. We need to create an excel sheet with the table name and the current date and extract the table records into the excel sheet.

For example

SQL server table name with the schema  

Excel sheet name

  

Data in Excel sheet. 

Let’s see how we will create this.

Below are the steps to complete this task.

Step 1: creating the Ado.net connection manager.

 

Step 2: creating the variable and storing the path of the destination folder where we are keeping the Excel files.  

Step 3: Taking Script task 

Inside the script task we are writing the below code.

Reading the ADO.Net connection string in this task.

SqlConnection myADONETConnection = new SqlConnection();                 myADONETConnection = (SqlConnection)(Dts.Connections["Ado_Dot_net_Conn"].AcquireConnection(Dts.Transaction) as SqlConnection);

Now we are reading the table and schema details from the connected database.

//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";                  SqlCommand cmd = new SqlCommand(query, myADONETConnection);                 DataTable dt = new DataTable();                 dt.Load(cmd.ExecuteReader());                 myADONETConnection.Close();

Now using below code we are looping each table and creating the excel sheet and reading the data from the database table and writing it into the excel sheet.

foreach (DataRow dt_row in dt.Rows)                 {                     string SchemaName = "";                     string TableName = "";                     object[] array = dt_row.ItemArray;                     SchemaName = array[0].ToString();                     TableName = array[1].ToString();                     string ExcelFileName = "";                     ExcelFileName = SchemaName + "_" + TableName + "_" + 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;\"";                     //Load Data into DataTable from SQL ServerTable                     string queryString = "SELECT * 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);                       //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();                 }

Below is the complete code for this task.

public void Main()                                 {         string datetime = DateTime.Now.ToString("yyyyMMdd");             try             {                 //Declare Variables for the destination folder                 string FolderPath = Dts.Variables["User::File_path"].Value.ToString();                 //USE ADO.NET Connection from SSIS Package to get data from table                 SqlConnection myADONETConnection = new SqlConnection();                 myADONETConnection = (SqlConnection)(Dts.Connections["Ado_Dot_net_Conn"].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";               SqlCommand cmd = new SqlCommand(query, myADONETConnection);                 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();                     string ExcelFileName = "";                     ExcelFileName = SchemaName + "_" + TableName + "_" + 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;\"";                     //Load Data into DataTable from SQL ServerTable                     string queryString = "SELECT * 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);                     //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::File_path"].Value.ToString() + "\\" + "ErrorLog_" + datetime + ".log"))                 {                     sw.WriteLine(exception.ToString());                     Dts.TaskResult = (int)ScriptResults.Failure;                 }             }          }

Now the script task is ready to run.

Before run see the list of table in the database.

 

Destination folder 

Now we are running this script.   

Package executed successfully.

  

Now see the excel sheet.

 

Get the expected result.