如何在公共静态 void 函数内访问 Dts 变量

huangapple go评论68阅读模式
英文:

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;

huangapple
  • 本文由 发表于 2023年4月10日 18:46:47
  • 转载请务必保留本文链接:https://go.coder-hub.com/75976419.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定