如何访问公共静态void函数中的Dts变量

Question

我在ssis的脚本任务中使用此代码将数据从一台服务器复制到另一台服务器。我不想硬编码服务器名和数据库名。包中有四个变量。它们是源服务器、源数据库、目的地服务器和目的地数据库。我在公共静态void函数中使用变量的方式是错误的。我已经在公共void main()函数中声明了所有变量。我不能使用这些变量在侧面公共静态void函数?我如何纠正这个问题?正确的使用方法是什么?有人能帮帮我吗?(我不是c#的家伙)谢谢

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.SqlClient;
#endregion
namespace ST_8fd22f0fcc0d441994ed6ab7e406b508
{

[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

public void Main()
{
string DestinationServer;
string DestinationDatabase;
string SourceServer;
string SourceDatabase;
DestinationServer = Dts.Variables["User::DestinationServer"].Value.ToString();
DestinationDatabase = Dts.Variables["User::DestinationDatabase"].Value.ToString();
SourceServer = Dts.Variables["User::SourceServer"].Value.ToString();
SourceDatabase = Dts.Variables["User::SourceDatabse"].Value.ToString();

//// string ConfigConnectionString = "Server= DestinationServer; Database= DestinationDatabase;Integrated Security = SSPI; ";
string ConfigConnectionString = $"Server={DestinationServer};Database={DestinationDatabase};Integrated Security=SSPI;";
string sql = "select SourceTable from ImportTableList";
// Create source connection
SqlConnection connection = new SqlConnection(ConfigConnectionString);
SqlDataReader dataReader;
try
{
connection.Open();
SqlCommand cmd = new SqlCommand(sql, connection);
dataReader = cmd.ExecuteReader();
while (dataReader.Read())
{
string Tbl = dataReader.GetValue(0).ToString();
//Move sql table
CopySQLTable(Tbl, true);
}
dataReader.Close();
cmd.Dispose();
connection.Close();
}
catch (Exception ex)
{
connection.Close();
}

}

public static void CopySQLTable(string SourceTable, bool OverwriteDestinationTable)
{

// string SourceconnectionString = "Server= SourceServer; Database= SourceDatabase ;Integrated Security = SSPI; ";
// string DestconnectionString = "Server= DestinationServer; Database= DestinationDatabase;Integrated Security = SSPI; ";
string SourceconnectionString =  $"Server={SourceServer};Database={SourceDatabase};Integrated Security=SSPI;";
string DestconnectionString = $"Server={DestinationServer};Database={DestinationDatabase};Integrated Security=SSPI;";
// Create source connection
SqlConnection source = new SqlConnection(SourceconnectionString);
// Create destination connection
SqlConnection destination = new SqlConnection(DestconnectionString);
SqlCommand cmd = null;
//delete from dest
if (OverwriteDestinationTable)
cmd = new SqlCommand("TRUNCATE TABLE " + SourceTable + "", destination);
source.Open();
destination.Open();
//delete from dest
if (OverwriteDestinationTable)
cmd.ExecuteNonQuery();
cmd = new SqlCommand("SELECT * FROM " + SourceTable + "", source);
// Open source and destination connections.
// Execute reader
SqlDataReader reader = cmd.ExecuteReader();
// Create SqlBulkCopy
SqlBulkCopy bulkData = new SqlBulkCopy(destination);
// Set destination table name
bulkData.DestinationTableName = SourceTable;
// Write data
bulkData.WriteToServer(reader);
// Close objects
bulkData.Close();
destination.Close();
source.Close();
}
#region ScriptResults declaration
/// <summary>
/// This enum provides a convenient shorthand within the scope of this class for setting the
/// result of the script.
/// 
/// This code was generated automatically.
/// </summary>
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion
}
}

我试图在公共静态void函数中声明变量。然而，当我把鼠标指针放在Dts上。变量，我得到一个错误消息:"在脚本任务的公共静态void函数中的dts变量"。有人能帮我修一下吗?

public static void CopySQLTable(string SourceTable, bool OverwriteDestinationTable, string SourceServer, string SourceDatabase, string DestinationServer, string DestinationDatabase)
{
DestinationServer = Dts.Variables["User::DestinationServer"].Value.ToString();
DestinationDatabase = Dts.Variables["User::DestinationDatabase"].Value.ToString();
SourceServer = Dts.Variables["User::SourceServer"].Value.ToString();
SourceDatabase = Dts.Variables["User::SourceDatabse"].Value.ToString();

string SourceconnectionString = $"Server={SourceServer};Database={SourceDatabase};Integrated Security=SSPI;";
string DestconnectionString = $"Server={DestinationServer};Database={DestinationDatabase};Integrated Security=SSPI;";

Answer 1

终于成功了。我以只读方式传递所有参数。这是最终代码。(只有代码的第一部分。你可以从原文中获得其余内容。

public void Main()
{
string DestinationServer;
string DestinationDatabase;
string SourceServer;
string SourceDatabase;
DestinationServer = Dts.Variables["User::DestinationServer"].Value.ToString();
DestinationDatabase = Dts.Variables["User::DestinationDatabase"].Value.ToString();
SourceServer = Dts.Variables["User::SourceServer"].Value.ToString();
SourceDatabase = Dts.Variables["User::SourceDatabse"].Value.ToString();
//// string ConfigConnectionString = "Server= DestinationServer; Database= DestinationDatabase;Integrated Security = SSPI; ";
string ConfigConnectionString = $"Server={DestinationServer};Database={DestinationDatabase};Integrated Security=SSPI;";
string sql = "select SourceTable from ImportTableList";
// Create source connection
SqlConnection connection = new SqlConnection(ConfigConnectionString);
SqlDataReader dataReader;
try
{
connection.Open();
SqlCommand cmd = new SqlCommand(sql, connection);
dataReader = cmd.ExecuteReader();
while (dataReader.Read())
{
string Tbl = dataReader.GetValue(0).ToString();
//Move sql table
CopySQLTable(Tbl, true, SourceServer, SourceDatabase, DestinationServer, DestinationDatabase);
}
dataReader.Close();
cmd.Dispose();
connection.Close();
}
catch (Exception ex)
{
connection.Close();
}

}
public static void CopySQLTable(string SourceTable, bool OverwriteDestinationTable, string SourceServer, string SourceDatabase, string DestinationServer, string DestinationDatabase)
{
string SourceconnectionString = $"Server={SourceServer};Database={SourceDatabase};Integrated Security=SSPI;";
string DestconnectionString = $"Server={DestinationServer};Database={DestinationDatabase};Integrated Security=SSPI;";
// Create source connection
SqlConnection source = new SqlConnection(SourceconnectionString);
// Create destination connection
SqlConnection destination = new SqlConnection(DestconnectionString);
SqlCommand cmd = null;