- To create an Excel file per tables from SQL Server Database. And create a Zip file that Excel file.
- The customer is a Database that contains tables like DEPT, EMP, etc.
- These table export into an excel sheet by using Script Task Component in SSIS.
- First, Create a variable Folder Path where you would like to export all the tables from a database to excel files.
- For creating variable right-click on whitespace area then go to the Variable and Click on Add Variable
- Specify the Variable Name as New, Data Type as String and folder path to the value.
- Also, Right-click on Connection Manager and Create an ADO.NET Connection.
- Then click on New →select the Server Name and Database Name →Click on OK.
- Drag and drop the Script task component and configure it.
- In Script, Select the Variable Name i.e. New.
- When clicking on Edit Script new pop-up window is open.
- Then we write the code for the export SQL Server tables from the Database to Excel file.
Code:-
Under #region Namespaces, I have added below code
using System.IO;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Runtime.InteropServices;
Under public void Main() { I have added the below code.
string datetime = DateTime.Now.ToString(“yyyyMMddHHmmss”);
try
{
//Declare Variables
string FolderPath = Dts.Variables[“User::New”].Value.ToString();
//USE ADO.NET Connection from SSIS Package to get data from table
SqlConnection myADONETConnection = new SqlConnection();
myADONETConnection = (SqlConnection)(Dts.Connections[“DB_Connection”].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();
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);
//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::New”].Value.ToString() + “\\” +
“ErrorLog_” + datetime + “.log”))
{
sw.WriteLine(exception.ToString());
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
}
- After done the coding run the code.
Create a zip file of the Excel sheet by using the 7-ZIP
- By using 7-ZIP we can zip or unzip the folders.
- After installing the 7-ZIP here is the located on.
- Excel file located on New folder.
- Drag and drop the Execute Process Task and Configure it.
- To configure component right-click on the Execute Process Task and Click on Edit option.
- In Executable browse the path of 7-Zip.
- In Arguments: a -t7z “D:\Excel\New.ZIP” “D:\Excel\New\*.*”
- To archive the files using the “a” command argument. The format is specified with the -t switch -t7z.
- In Working Directory browse the path of the folder that contains executable.
- Then click on OK. and Run the Package.
- After running the package the zip folder was created.
Start with MSBI video as mentioned below to learn topic practically in-depth: