CODEWITHSUNDEEP
vb.netvisual-studio-2010vb6ssissmtp
user1254579
user1254579

Reputation: 4009

how to execute data command that returns a result set in Vb.net SSIS Script Task

it has been found that the sql queries can be executed inside the vb.net.The below script work well

Public Sub Main()

    ''mail variables

    Dim myHtmlMessage As MailMessage
    Dim mySmtpClient As SmtpClient
    Dim value As NetworkCredential


    ''sql variables
    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(*) from table", sqlConn)
    rowsAffected = sqlComm.ExecuteScalar()

    ''sqlComm.ExecuteNonQuery() if you do not want to return anything 

    ''Inform SSIS you're done your work
    cm.ReleaseConnection(sqlConn)
    Dts.Events.FireInformation(0, "", rowsAffected.ToString() + " rows updated.", "", 0, fireAgain)
    'MsgBox(rowsAffected.ToString())





    myHtmlMessage = New MailMessage("email", "email", "This is a testing from  VB script Task", rowsAffected.ToString)
    mySmtpClient = New SmtpClient("send.company.net")
    mySmtpClient.Port = 585
    value = New NetworkCredential("email", "pwd") ''this is the line added


    mySmtpClient.Credentials = value
    mySmtpClient.EnableSsl = True
    mySmtpClient.Send(myHtmlMessage)
    Dts.TaskResult = ScriptResults.Success


End Sub

My question is what if i want to execute data command that returns a result set in Vb.net SSIS Script Task? in the first script there is a varibale called rowsAffected ,which holds the count. But in the 2nd script i need to get the result set which consists of more than 12 rows .what modification can i do for the blow script ? is it like need to implement an array of string ?

Public Sub Main()

    ''mail variables

    Dim myHtmlMessage As MailMessage
    Dim mySmtpClient As SmtpClient
    Dim value As NetworkCredential


    ''sql variables
    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 n.c as [Total_Row_inserted_by this_Load],reverse(substring(reverse(n.[SourceFileName]),1,9)) Filename        from()(SELECT count(*) c, [SourceFileName]  FROM [Testing-DB].[dbo].[AE_Data]  group by [SourceFileName]", sqlConn)**
    **rowsAffected = sqlComm.ExecuteScalar()**

    ''sqlComm.ExecuteNonQuery() if you do not want to return anything 

    ''Inform SSIS you're done your work
    cm.ReleaseConnection(sqlConn)
    Dts.Events.FireInformation(0, "", rowsAffected.ToString() + " rows updated.", "", 0, fireAgain)
    'MsgBox(rowsAffected.ToString())





    myHtmlMessage = New MailMessage("email", "email", "This is a testing from  VB script Task", rowsAffected.ToString)
    mySmtpClient = New SmtpClient("send.company.net")
    mySmtpClient.Port = 585
    value = New NetworkCredential("email", "pwd") ''this is the line added


    mySmtpClient.Credentials = value
    mySmtpClient.EnableSsl = True
    mySmtpClient.Send(myHtmlMessage)
    Dts.TaskResult = ScriptResults.Success


End Sub

Upvotes: 0

Views: 3111

Answers (1)

S.M
S.M

Reputation: 776

To store results in string array. You can use this sample block of code in place of those commented code and adjust the sql statement accordingly. you can store these results in DataTable or in Multidimentional array also.

    sqlComm = New System.Data.SqlClient.SqlCommand("select Column1 from table ", sqlConn)
    Dim sqlReader As System.Data.SqlClient.SqlDataReader
    Dim arrList As New ArrayList()

    sqlReader = sqlComm.ExecuteReader
    Do While sqlReader.Read
        arrList.Add(sqlReader.GetString(0))
    Loop

Upvotes: 1

Related Questions