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.
