开发者

SSIS Script transformation error using ADO.NET

I'm getting an error that states Incorrect syntax near ')'. when executing my SSIS package. The error is being thrown by a Script transformation component in the DFT. From what I can tell it is being thrown by the sqlReader = sqlCmd.ExecuteReader() line. I have been looking at it for days now and can't seem to figure out what is causing the error. I've included the contents of the script component below. Anyone have any ideas?

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Data.SqlClient

Public Class ScriptMain
Inherits UserComponent

Dim connMgr As IDTSConnectionManager90
Dim sqlConn As SqlConnection
Dim sqlCmd As SqlCommand
Dim sqlParam As SqlParameter

Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

    connMgr = Me.Connections.connTalisma
    sqlConn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)

End Sub

Public Overrides Sub PreExecute()

    sqlCmd = New SqlCommand("SELECT Name FROM dbo.Category WHERE CatID = @catid)", sqlConn)
    sqlParam = New SqlParameter("@catid", SqlDbType.Int)
    sqlCmd.Parameters.Add(sqlParam)

End Sub

Public Overrides Sub ReleaseConnections()

    connMgr.ReleaseConnection(sqlConn)

End Sub

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
    Dim sqlReader As SqlDataReader
    Dim delimitedField As String = Row.FinalCallReason
    Dim delimiter As String = ";"
    Dim tempField As String

    If Not (String.IsNullOrEmpty(delimitedField)) Then
        Dim DelimitedListArray() As String = delimitedField.Split(New String() {delimiter}, StringSplitOptions.RemoveEmptyEntries)
        For Each item As String In DelimitedListArray
            sqlCmd.Parameters("@catid").Value = CInt(item)
            MsgBox(item)
            Try
                sqlReader = sqlCmd.ExecuteReader()
                tempField = tempField 开发者_JS百科+ ";" + sqlReader.GetString(0)
            Catch e As Exception
                MsgBox("Error: " + e.Message)
            End Try
        Next
    End If
    Row.FinalCallReason = tempField
End Sub

End Class


In the following section of the code, change @catid) to @catid in the SqlCommand object initialization statement.

Remove the parentheses. It is not needed. The parentheses is causing the query to fail and hence the error message Incorrect syntax near ')'.

Public Overrides Sub PreExecute()

    sqlCmd = New SqlCommand("SELECT Name FROM dbo.Category WHERE CatID = @catid)", sqlConn)
    sqlParam = New SqlParameter("@catid", SqlDbType.Int)
    sqlCmd.Parameters.Add(sqlParam)

End Sub
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜