首页 文章

脚本任务填充:尚未初始化SelectCommand.Connection属性

提问于
浏览
2

尝试使用SSIS连接到TSQL数据库时,我收到以下错误,以便将数据导出到Excel文件 .

System.InvalidOperationException:Fill:SelectCommand.Connection属性尚未初始化 . 在System.Data.Common.DbDataAdapter.GetConnection3(DbDataAdapter的适配器,IDbCommand的命令,字符串方法)在System.Data.Common.DbDataAdapter.FillInternal(数据集的数据集,数据表[]数据表,的Int32 startRecord,的Int32最大记录,字符串srcTable要,IDbCommand的命令,System.Data.Common.DbDataAdapter.Fill(DataSet dataSet,Int32 startRecord,Int32 maxRecords,String srcTable,IDbCommand命令,CommandBehavior行为),位于System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)的ST_e8dfb5f825c943ddab64a2531021cddf处的CommandBehavior行为 . 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 SQL Server 2012上使用ASP.NET 4.0

1 回答

  • 1

    尝试在创建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);
    

相关问题