How do I execute a stored procedure in a script task using vb.net? I'd like to use a script task as opposed to an execute sql task because I have conditional logic and I have lots of input parameters.
I'm using the code below but receive this error when calling the sub:
Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.SqlClient.SqlConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.
Public Sub fRunStoredProc()
Dim cn As SqlConnection = DirectCast(Dts.Connections("MyConnection").AcquireConnection(Nothing), SqlConnection)
Dim cmd As SqlCommand
cmd.Connection = cn
cmd.CommandText = "[dbo].[MyStoredProcedure]"
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("FileName", DbType.String).Value = "MyFileName"
cmd.Parameters.AddWithValue("Title", DbType.String).Value = "MyTitle"
cmd.Parameters.AddWithValue("ReturnID", DbType.Int32).Direction = ParameterDirection.Output