Executing SQL from Script Component + SSIS

Question

Am trying to execute sql from script component, but getting error when trying to establish SQL connection. My Code below in Script Component under PreExecute()

base.PreExecute();
    variables = null;
    VariableDispenser.LockForWrite("User::TableName");
    VariableDispenser.LockForWrite("User::SqlConnectionString");
    VariableDispenser.GetVariables(out variables);
    tableName = variables[0].Value.ToString();
    sqlConnection = variables[1].Value.ToString();

    dt = new DataTable();

    //sqlConnection = Data Source=LOCALHOST;Initial Catalog=MergeTestDestination;Integrated Security=True;
    using (SqlConnection cnn = new SqlConnection(sqlConnection))
    {
        try
        {
            cnn.Open();
            MessageBox.Show("We are connected.....");
            cnn.Close();
        }
        catch(Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }

Error:

at System.Data.Common.DbConnectionOptions.GetKeyValuePair(String connectionString, Int32 currentPosition, StringBuilder buffer, Boolean useOdbcRules, String& keyname, String& keyvalue) at System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable synonyms, Boolean firstKey) at System.Data.Common.DbConnectionOptions..ctor(String connectionString, Hashtable synonyms, Boolean useOdbcRules) at System.Data.SqlClient.SqlConnectionString..ctor(String connectionString) at System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous) at System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(String connectionString, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions) at System.Data.SqlClient.SqlConnection.ConnectionString_Set(String value) at System.Data.SqlClient.SqlConnection.set_ConnectionString(String value) at System.Data.SqlClient.SqlConnection..ctor(String connectionString) at ScriptMain.PreExecute() at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PreExecute()

what am i doing wrong ? Any help ?

Thanks

unclepaul84 unclepaul84 · Accepted Answer · 2010-02-17T16:18:17

Put the following code before the connection constructor Debug.Assert(!string.IsNullOrEmpty(sqlConnection));

Executing SQL from Script Component + SSIS

1 Answers