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
精彩评论