Export SQL Server Tables From database to Excel File in SSIS

• 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.

DATABASE

• 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

VARIABLE

• Specify the Variable Name as New, Data Type as String and folder path to the value.

CREATE VARIABLE

• Also, Right-click on Connection Manager and Create an ADO.NET Connection.

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.

SCRIPT TASK COMPONENT

• 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.

7-ZIP LOCATION

• Excel file located on New folder.

EXCEL FILE

• Drag and drop the Execute Process Task and Configure it.

EXECUTE PROCESS TASK

• 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: