CODEWITHSUNDEEP
vb.netvisual-studio-2010sql-server-2008ado.netssis
user1254579
user1254579

Reputation: 4009

executing sql inside ssis script task is not working

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?

how to get the correct count value using this below vb codes(please have a look) in ssis script task?

Public Sub Main()

    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

Thanks

Upvotes: 1

Views: 10859

Answers (2)

mr.Reband
mr.Reband

Reputation: 2430

When you use ExecuteNonQuery, you are saying that you do not require any results back from the query.

To read the result from your query, use ExecuteScalar:

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar.aspx

If your query returns more than one cell, use ExecuteReader and a SQLDataReader object:

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.aspx

Upvotes: 4

bastos.sergio
bastos.sergio

Reputation: 6764

ExecuteNonQuery returns the number of affected rows by the query. In the case of the COUNT function it always returns -1.

For what you are trying to achieve, you should be using sqlComm.ExecuteScalar() which returns the value of the first row/column from the resultset.

Upvotes: 3

Related Questions