将SQL表输出到脚本任务中SSIS/C#中的现有Excel文件/新工作表

Question

你好，

我收到一个创建SSIS包的请求，该包将把SQL表输出到Excel文件。我创建这个没有问题。然而，客户回来询问他们希望能够将SQL表内容输出到新工作表中的现有Excel文件。如果工作表在我的以下脚本中不存在，则它正在创建中。然而，它只是回到循环中并失败，因为现在它已经存在了。

这是我的代码：

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[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["$Package::FolderPath"].Value.ToString() + "\" + Dts.Variables["$Package::ExcelFileName"].Value.ToString() + "_" + datetime + ".log"))
{
sw.WriteLine(exception.ToString());
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
}

有人能帮我做这个吗？我对C#还很陌生，英语不是我的主要语言。如果这还不够清楚，请告诉我。

提前感谢您的时间：-(Mylene

Answer 1

你知道当你试图找到解决方案时，它就在你面前吗？

如果用户试图添加一个已经存在的工作表，我的代码在想要捕捉错误之前，已经在这些情况下清除了一个异常。。。

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();
ExcelFileName = ExcelFileName + "_" + datetime;
string lastChar = FolderPath.Substring(FolderPath.Length - 1);

//Validate format of FolderPath
if (lastChar != "\")
{
FolderPath = FolderPath + "\";
}

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;"";
//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;
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)
{
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[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["$Package::FolderPath"].Value.ToString() + "\" + Dts.Variables["$Package::ExcelFileName"].Value.ToString() + datetime + ".log"))
{
sw.WriteLine(exception.ToString());
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
}
}

}

错误日志：System.Data.OleDb.OleDbException(0x80040E14(：表"Test9"已存在。在System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult-hr(位于System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS-dbParams，Object&executeResult(位于System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object&executeResult(位于System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior行为，String方法(位于System.Data.OleDb.OleDbCommand.ExecuteNonQuery((位于ST_a210075701434669391932c30b7.ScriptMain.Main((

问题已解决。