SSIS Script Task- Connecting the ADO.NET and populating DataTable

Question

I need to connect to a SQL Server db thru a script task in order to populate a DataTable, I'm using ADO.Net provider/connection. However for the life of me I'm getting all sorts of errors. For example, when using the SqlAdapter I get an invalid object error, however the SqlCommand executes without errors in SSMS:

SqlConnection conn;
ConnectionManager cm;
SqlCommand cmd;

cm = Dts.Connections["AdoNet"];
conn = (SqlConnection)cm.AcquireConnection(Dts.Transaction);

using (conn)
{   
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = conn;
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = queryString;
    SqlDataAdapter da = new SqlDataAdapter();
    da.SelectCommand = cmd;
    da.Fill(myDataTable);
}

Answer 1

Try the following code:

Using(SqlConnection conn = (SqlConnection)Dts.Connections["AdoNet"].AcquireConnection(Dts.Transaction)){

if (conn.State != ConnectionState.Open){
 conn.Open();}

SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = queryString;
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(myDataTable);
}