how to declare variables in public static void function

Question

Hi all, 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

	}
}

Answer 1

You can add parameters to your function, for example:

// in Main:

CopySQLTable(Tbl, true, DestinationServer, DestinationDatabase, SourceServer, SourceDatabase);


// the function:

public static void CopySQLTable(string SourceTable, bool OverwriteDestinationTable,
   string DestinationServer, string DestinationDatabase,
   string SourceServer, string SourceDatabase)
{
   . . .
}

Or you can move the variables to class level, without other adjustments:

public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
	{
        static string DestinationServer;
        static string DestinationDatabase;
        static string SourceServer;
        static string SourceDatabase;
        
        public void Main()
        {
           // (variables moved)
           . . .           

The latter method depends on some aspects. Maybe the Main function can be declared static too?

Answer 2

Finally it works. I pass all my parameters as read only. This is the final code if anyone is interested. You can get the rest of the code 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;