title: SSIS Script transformation error using ADO.NET
body: <p>I'm getting an error that states <code>Incorrect syntax near ')'.</code> 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 <code>sqlReader = sqlCmd.ExecuteReader()</code> 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?</p>
  
  <pre><code>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 + ";" + sqlReader.GetString(0)
              Catch e As Exception
                  MsgBox("Error: " + e.Message)
              End Try
          Next
      End If
      Row.FinalCallReason = tempField
  End Sub
  
  End Class
  </code></pre>