英文:
how to access the Dts variables inside the public static void function
问题
I use this code in script task in SSIS to copy data from one server to another. I don't want to hardcode the server name and database name. There are four variables in the package. They are SourceServer, SourceDatabase, DestinationServer, and DestinationDatabase.
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::SourceDatabase"].Value.ToString();
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;";
// Rest of the code...
}
I hope this helps you with your code!
英文:
I use this code in script task in ssis to copy data from one server to another. I don't want to hardcode the server name and database name. There are four variables in the package. They are SourceServer, SourceDatabase, DestinationServer and DestinationDatabase. The way I use variables in public static void function is wrong. I have declared all variables inside public void main() function. Can't I use these variables in sdie public static void function? How do I correct this? what is the right way to use it? Can anyone help me please. (I am not a c# guy ) Thanks
#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
}
}
I tried to declare the variables inside public static void function. However when I hove the mouse pointer over Dts.Variables, I get an error message."dts variable in public static void function in script task".
Can someone please help me to fix this?
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;";
答案1
得分: 0
Here is the translated code:
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::SourceDatabase"].Value.ToString();
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;
}
英文:
Finally, It works. I pass all parameters as read only. This is the final code.( Only he first part of the code. You can get the rest from the original post.)
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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论