M
MChalut
Guest
Good day,
I had a request to create a SSIS package that would output an SQL table to an Excel file. I had no problem creating this. However, the client came back asking that they wanted to be able to output the SQL table content to an existing Excel file in a new worksheet. If the worksheet does not exists in my following script, it is being created. However, it just goes back in the loop and fail because now it exists.
Here is my code:
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
#endregion
namespace ST_a21007570143466693913591932c30b7
{
/// <summary>
/// ScriptMain is the entry point class of the script. Do not change the name, attributes,
/// or parent of this class.
/// </summary>
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region ScriptResults declaration
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
public void Main()
{
string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
try
{
//Declare Variables
string ExcelFileName = Dts.Variables["$Package::ExcelFileName"].Value.ToString();
string FolderPath = Dts.Variables["$Package::FolderPath"].Value.ToString();
string TableName = Dts.Variables["$Package::SQLTableName"].Value.ToString();
string SchemaName = Dts.Variables["$Package::SQLTableSchema"].Value.ToString();
string SheetName = Dts.Variables["$Package::SheetName"].Value.ToString();
string lastChar = FolderPath.Substring(FolderPath.Length - 1);
string currentTab;
DataTable ExcelFileTabs;
//Validate format of FolderPath
if (lastChar != "\\")
{
FolderPath = FolderPath + "\\";
}
string FullExcelFilePath = FolderPath + ExcelFileName + ".xlsx";
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=" + FullExcelFilePath + ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";
//USE ADO.NET Connection from SSIS Package to get data from table
SqlConnection myADONETConnection = new SqlConnection();
myADONETConnection = (SqlConnection)(Dts.Connections["ADO_DBConnection"].AcquireConnection(Dts.Transaction) as SqlConnection);
//Load Data into DataTable from SQL ServerTable
// Assumes that connection is a valid SqlConnection object.
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;
// Verify if file exists
if (File.Exists(FullExcelFilePath))
{
//Verify if the sheet exists
foreach (DataTable table in ds.Tables)
{
ExcelFileTabs = Excel_OLE_Con.GetSchema("Tables");
foreach (DataRow excelTable in ExcelFileTabs.Rows)
{
currentTab = excelTable["TABLE_NAME"].ToString();
if (currentTab == SheetName)
{
// Create Log File for Errors
using (StreamWriter sw = File.CreateText(Dts.Variables["$Package::FolderPath"].Value.ToString() + "\\" + Dts.Variables["$Package::ExcelFileName"].Value.ToString() + "_" + datetime + ".log"))
{
sw.WriteLine("The sheet " + SheetName + " that your are trying to create in " + FullExcelFilePath + " already exists.");
sw.WriteLine("Please enter another sheet name or delete the Excel file and try again.");
}
Excel_OLE_Con.Close();
Dts.TaskResult = (int)ScriptResults.Failure;
}
else
{
// Create the worksheet in the existing Excel file
Excel_OLE_Cmd.CommandText = "Create table " + SheetName + " (" + TableColumns + ")";
Excel_OLE_Cmd.ExecuteNonQuery();
}
}
}
}
else
{
Excel_OLE_Cmd.CommandText = "Create table " + SheetName + " (" + TableColumns + ")";
Excel_OLE_Cmd.ExecuteNonQuery();
}
//Write Data to Excel Sheet from DataTable dynamically
foreach (DataTable table in ds.Tables)
{
ExcelFileTabs = Excel_OLE_Con.GetSchema("Tables");
foreach (DataRow excelTable in ExcelFileTabs.Rows)
{
String sqlCommandInsert = "";
String sqlCommandValue = "";
foreach (DataColumn dataColumn in table.Columns)
{
sqlCommandValue += dataColumn + "],[";
}
sqlCommandValue = "[" + sqlCommandValue.TrimEnd(',');
sqlCommandValue = sqlCommandValue.Remove(sqlCommandValue.Length - 2);
sqlCommandInsert = "INSERT into " + SheetName + "(" + sqlCommandValue.TrimEnd(',') + ") 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 + "',";
}
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["$Package::FolderPath"].Value.ToString() + "\\" + Dts.Variables["$Package::ExcelFileName"].Value.ToString() + "_" + datetime + ".log"))
{
sw.WriteLine(exception.ToString());
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
}
}
}
Can somebody please help me with this ? I am pretty new to C#, and English is not my primary language. Please let me know if this is not clear enough.
Thanks in advance for you time
Mylene
Continue reading...
I had a request to create a SSIS package that would output an SQL table to an Excel file. I had no problem creating this. However, the client came back asking that they wanted to be able to output the SQL table content to an existing Excel file in a new worksheet. If the worksheet does not exists in my following script, it is being created. However, it just goes back in the loop and fail because now it exists.
Here is my code:
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
#endregion
namespace ST_a21007570143466693913591932c30b7
{
/// <summary>
/// ScriptMain is the entry point class of the script. Do not change the name, attributes,
/// or parent of this class.
/// </summary>
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
#region ScriptResults declaration
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
public void Main()
{
string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
try
{
//Declare Variables
string ExcelFileName = Dts.Variables["$Package::ExcelFileName"].Value.ToString();
string FolderPath = Dts.Variables["$Package::FolderPath"].Value.ToString();
string TableName = Dts.Variables["$Package::SQLTableName"].Value.ToString();
string SchemaName = Dts.Variables["$Package::SQLTableSchema"].Value.ToString();
string SheetName = Dts.Variables["$Package::SheetName"].Value.ToString();
string lastChar = FolderPath.Substring(FolderPath.Length - 1);
string currentTab;
DataTable ExcelFileTabs;
//Validate format of FolderPath
if (lastChar != "\\")
{
FolderPath = FolderPath + "\\";
}
string FullExcelFilePath = FolderPath + ExcelFileName + ".xlsx";
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=" + FullExcelFilePath + ";" + "Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";
//USE ADO.NET Connection from SSIS Package to get data from table
SqlConnection myADONETConnection = new SqlConnection();
myADONETConnection = (SqlConnection)(Dts.Connections["ADO_DBConnection"].AcquireConnection(Dts.Transaction) as SqlConnection);
//Load Data into DataTable from SQL ServerTable
// Assumes that connection is a valid SqlConnection object.
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;
// Verify if file exists
if (File.Exists(FullExcelFilePath))
{
//Verify if the sheet exists
foreach (DataTable table in ds.Tables)
{
ExcelFileTabs = Excel_OLE_Con.GetSchema("Tables");
foreach (DataRow excelTable in ExcelFileTabs.Rows)
{
currentTab = excelTable["TABLE_NAME"].ToString();
if (currentTab == SheetName)
{
// Create Log File for Errors
using (StreamWriter sw = File.CreateText(Dts.Variables["$Package::FolderPath"].Value.ToString() + "\\" + Dts.Variables["$Package::ExcelFileName"].Value.ToString() + "_" + datetime + ".log"))
{
sw.WriteLine("The sheet " + SheetName + " that your are trying to create in " + FullExcelFilePath + " already exists.");
sw.WriteLine("Please enter another sheet name or delete the Excel file and try again.");
}
Excel_OLE_Con.Close();
Dts.TaskResult = (int)ScriptResults.Failure;
}
else
{
// Create the worksheet in the existing Excel file
Excel_OLE_Cmd.CommandText = "Create table " + SheetName + " (" + TableColumns + ")";
Excel_OLE_Cmd.ExecuteNonQuery();
}
}
}
}
else
{
Excel_OLE_Cmd.CommandText = "Create table " + SheetName + " (" + TableColumns + ")";
Excel_OLE_Cmd.ExecuteNonQuery();
}
//Write Data to Excel Sheet from DataTable dynamically
foreach (DataTable table in ds.Tables)
{
ExcelFileTabs = Excel_OLE_Con.GetSchema("Tables");
foreach (DataRow excelTable in ExcelFileTabs.Rows)
{
String sqlCommandInsert = "";
String sqlCommandValue = "";
foreach (DataColumn dataColumn in table.Columns)
{
sqlCommandValue += dataColumn + "],[";
}
sqlCommandValue = "[" + sqlCommandValue.TrimEnd(',');
sqlCommandValue = sqlCommandValue.Remove(sqlCommandValue.Length - 2);
sqlCommandInsert = "INSERT into " + SheetName + "(" + sqlCommandValue.TrimEnd(',') + ") 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 + "',";
}
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["$Package::FolderPath"].Value.ToString() + "\\" + Dts.Variables["$Package::ExcelFileName"].Value.ToString() + "_" + datetime + ".log"))
{
sw.WriteLine(exception.ToString());
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
}
}
}
Can somebody please help me with this ? I am pretty new to C#, and English is not my primary language. Please let me know if this is not clear enough.
Thanks in advance for you time
Mylene
Continue reading...