title: executing sql inside ssis script task is not working
body: <p>Trying to execute the sql statement inside the vb.net (script task ssis).For this created the connections,requsted open connection and executed and closed(as shown in the below code).The value of a variable (which holds the sql result set) has to be returned a correct count,instead it returned  -1 all the times ...any idea y does it so?Seems to be the query is not executing?</p>
  
  <p>how to get the correct count value using this below vb codes(please have a look)
   in ssis script task?</p>
  
  <p>Public Sub Main()</p>
  
  <pre><code>    Dim fireAgain As Boolean = True
      Dim rowsAffected As Integer
  
      Dim sqlConn As System.Data.SqlClient.SqlConnection
      Dim sqlComm As System.Data.SqlClient.SqlCommand
  
  
      Dim cm As ConnectionManager = Dts.Connections("cnn") ''Retrive the reference to the managed Connections
  
      '' Request an open connection
      sqlConn = cm.AcquireConnection(Dts.Transaction)
      Dts.Events.FireInformation(0, "", "Connection is: " + sqlConn.State.ToString(), "", 0, fireAgain)
  
      ''Do your work
  
      sqlComm = New System.Data.SqlClient.SqlCommand("SELECT COUNT(*) AS CNT  FROM [Table_1]", sqlConn)
      rowsAffected = sqlComm.ExecuteNonQuery()
  
      MsgBox(rowsAffected.ToString())  '''' the value of variable is -1 ???
  
      ''Inform SSIS you're done your work
      cm.ReleaseConnection(sqlConn)
  
      Dts.Events.FireInformation(0, "", rowsAffected.ToString() + " rows updated.", "", 0, fireAgain)
  
  
  
  End Sub
  </code></pre>
  
  <p>Thanks</p>