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);
}
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);
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With