尝试使用SSIS连接到TSQL数据库以便将数据导出到excel文件时出现以下错误。
System.InvalidOperationException：填充：SelectCommand.Connection属性尚未初始化。
在System.Data.Common.DbDataAdapter.GetConnection3（DbDataAdapter适配器，IDbCommand命令，String方法）
在System.Data.Common.DbDataAdapter.FillInternal处（DataSet数据集，DataTable []数据表，Int32 startRecord，Int32 maxRecords，字符串srcTable，IDbCommand命令，CommandBehavior行为）
在System.Data.Common.DbDataAdapter.Fill（DataSet dataSet，Int32 startRecord，Int32 maxRecords，字符串srcTable，IDbCommand命令，CommandBehavior行为）
在System.Data.Common.DbDataAdapter.Fill（DataSet dataSet）
在ST_e8dfb5f825c943ddab64a2531021cddf.ScriptMain.Main（）
下面是我的代码：
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Data.OleDb;
using System.Data.SqlClient;
#endregion
namespace ST_e8dfb5f825c943ddab64a2531021cddf
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public void Main()
{
// TODO: Add your code here
string datetime=DateTime.Now.ToString("yyyyMMddHHmmss");
try
{
//Declare Variables
string ExcelFileName = Dts.Variables["User::ExcelFileName"].Value.ToString();
string FolderPath = Dts.Variables["User::FolderPath"].Value.ToString();
string TableName = Dts.Variables["User::TableName"].Value.ToString();
string SheetName = Dts.Variables["User::SheetName"].Value.ToString();
ExcelFileName = ExcelFileName + "_" + 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;\"";
//drop Excel file if exists
File.Delete(FolderPath + "\\" + ExcelFileName + ".xlsx");
//USE ADO.NET Connection from SSIS Package to get data from table
SqlConnection myADONETConnection = new SqlConnection();
myADONETConnection = (SqlConnection)(Dts.Connections["DBConn"].AcquireConnection(Dts.Transaction) as SqlConnection);
//Load Data into DataTable from SQL ServerTable
// Assumes that connection is a valid SqlConnection object.
string queryString =
"SELECT * from " + 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 " + 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["User::FolderPath"].Value.ToString() + "\\" +
Dts.Variables["User::ExcelFileName"].Value.ToString() + datetime + ".log"))
{
sw.WriteLine(exception.ToString());
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
}
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
}
}
谁能帮助我了解我在做什么错。先感谢您！
在Visual Studio 2010 Shell中使用ASP.NET 4.0
SQL Server 2012
最佳答案
创建sqlconnection对象时，尝试删除= new SqlConnection();，并在使用它之前检查连接是否已打开，您的代码应如下所示：
SqlConnection myADONETConnection = (SqlConnection)(Dts.Connections["DBConn"].AcquireConnection(Dts.Transaction) as SqlConnection);
If (myADONETConnection.State != ConnectionState.Open){
myADONETConnection.Open();
}
string queryString =
"SELECT * from " + TableName;
SqlDataAdapter adapter = new SqlDataAdapter(queryString, myADONETConnection);
DataSet ds = new DataSet();
adapter.Fill(ds);