How to connect to SQL database inside of Script Task in SSIS using C#

This example shows how to execute a store procedure inside of a SSIS Script Task:

// Get the connection from the Dts package

Microsoft.SqlServer.Dts.Runtime.ConnectionManager cm = Dts.Connections["connectionManaget1"];

// Create the connection

System.Data.SqlClient.SqlConnection sqlConn = (System.Data.SqlClient.SqlConnection)cm.AcquireConnection(Dts.Transaction);

// Create the command with the connection

System.Data.SqlClient.SqlCommand sqlComm = new System.Data.SqlClient.SqlCommand("EXEC dbo.MyStoreProcedure @var = 1", sqlConn);

// Execute the command

sqlComm.ExecuteNonQuery();

// Release the connection

cm.ReleaseConnection(sqlConn);