how to use SSIS variables in script task connection string

Question

Hi all, I am using the following code (This is only the part of the code) to connect to sql db. I am using this in SSIS script task. Inset of hard coded connection string, I want to use variables. There are four variables in SSIS package. They are SourceSever, SourceDatabase, DestinationServer, DestinationDatabase. I have selected them as readonly variables in script task. However my code does not work when I replace hardcoded values with variables. How can i achieve this? This is the part of the code.(C#) Thanks

public void Main()
        {
            string ConfigConnectionString = "Server= User::DestinationServer; Database= User::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();

Answer 1

Hi Nishan Ratna, Please try the following two options.

• First option is using string interpolation. It was introduced in c# v.6
• Second option is using so called string composite formatting.

string DestinationServer = Dts.Variables["$User::DestinationServer"].Value.ToString();
string DestinationDatabase = Dts.Variables["$User::DestinationDatabase"].Value.ToString();

// option #1
string ConfigConnectionString = $"Server={DestinationServer};Database={DestinationDatabase};Integrated Security=SSPI;";

// or option #2
string ConfigConnectionString = string.Format("Server={0};Database={1};Integrated Security=SSPI;"
	, DestinationServer
	, DestinationDatabase);