How to execute SqlCommand using OLEDB connection in SSIS script component

Using a Script Component

To access a connection manager within a script component, you should first specify this connection manager from the script component editor as shown in the image below:

Then, within the script component, you should use the Connections variable to access this connection manager (In this example, the connection manager assigned name is Connection):

var constr  = Connections.Connection.ConnectionString;

screenshot

Executing a SQL command using C#

Next, you should use this connection string to initiate a SqlConnection object in order to use it by a SqlCommand as follows:

using(SqlConnection conn = new SqlConnection(constr))
{
    using (SqlCommand cmd = new SqlCommand("Write here your SQL command", conn))
    {
        conn.Open();
        cmd.ExecuteNonQuery();
    }
}

Make sure you added a reference for using System.Data.SqlClient to use the SqlConnection and SqlCommand objects.

Important Note: If you are using SQL authentication, you should re-add the password to the connection string since it will not be retrieved from the connection manager for security reasons.

Using a Script Task

You should first retrieve the OLE DB connection string from the connection manager using the following command:

string constr = Dts.Connections["OledbCM"].ConnectionString;

Demonstration

I added Messagebox.Show(constr) command to the script task to show how the connection string looks like once retrieved:

Helpful Links:

• SSIS Script Task : ConnectionString for ADO.Net & OleDb ConnectionManager
• Check OLDEDB database Connection using script task in SSIS

Update 1: SqlConnection vs. OleDbConnection

Since you are using SqlConnection class which represents a connection to a SQL Server database. You will encounter the following error while trying to use the connection string retrieved from an OleDb Connection:

Keyword not supported "Provider"

The main reason is that the OleDB connection requires that the connection string contains the provider information since it has the ability to connect to different providers such as Oracle, Excel, Access, SQL. While SqlConnection class does not support this information since it is only using the SQL Server Native client provider.

How to solve this issue?

You can simply use a System.Data.OleDb.OleDbConnectionStringBuilder object to remove this part using the following code:

string con = Dts.Connections["OledbCM"].ConnectionString;
var connBldr = new OleDbConnectionStringBuilder(con);
connBldr.Remove("Provider");
con  = connBldr.ConnectionString;

Or you can use Linq: (Remember to add the System.Linq namespace)

string con = Dts.Connections["OledbCM"].ConnectionString;
con = string.Join(";", con.Split(';').Where(c =>
    !c.TrimStart().StartsWith("Provider")));

References

• How do I remove "Provider=..." from a connection string