get return value from stored procedure, execute within ssis script task vb.net

• ipisors 92539

  SSC-Addicted

  Points: 445

  More actions

  October 1, 2018 at 2:11 pm

  #368913

  I've been searching around a LOT, and - although I'm come hairline close - can't seem to quite find how to simply execute a T-SQL stored procedure with a RETURN VALUE, and get that RETURN VALUE - all within the SSIS script task, I'd like to use VB.NET.

  I already have code like this:

    Dim mConn As OleDb.OleDbConnection = DirectCast(Dts.Connections("connection name").AcquireConnection(Nothing), OleDb.OleDbConnection)
     Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand()
     cmd.Connection = mConn
     cmd.CommandType = CommandType.StoredProcedure

  Basically, I currently have functional code that executes a stored procedure which ends up inserting a record to a table.  I then have a SECOND stored procedure which returns the IDENT_CURRENT from the table in question.  
  I want to execute and get the value from that SECOND stored procedure, which is:

  create procedure [dbo].[procname]
  as
  begin
      declare @identity int
      set @identity = IDENT_CURRENT('[dbo].[tablename]')
      return @identity
  end

• Jonathan AC Roberts

  SSCoach

  Points: 18407

  More actions

  October 1, 2018 at 2:26 pm

  #2007601

  ipisors 92539 - Monday, October 1, 2018 2:11 PM

  I've been searching around a LOT, and - although I'm come hairline close - can't seem to quite find how to simply execute a T-SQL stored procedure with a RETURN VALUE, and get that RETURN VALUE - all within the SSIS script task, I'd like to use VB.NET.

  I already have code like this:

    Dim mConn As OleDb.OleDbConnection = DirectCast(Dts.Connections("connection name").AcquireConnection(Nothing), OleDb.OleDbConnection)
     Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand()
     cmd.Connection = mConn
     cmd.CommandType = CommandType.StoredProcedure

  Basically, I currently have functional code that executes a stored procedure which ends up inserting a record to a table.  I then have a SECOND stored procedure which returns the IDENT_CURRENT from the table in question.  
  I want to execute and get the value from that SECOND stored procedure, which is:

  create procedure [dbo].[procname]
  as
  begin
      declare @identity int
      set @identity = IDENT_CURRENT('[dbo].[tablename]')
      return @identity
  end

  You can put 3 commands in the CommandText:

  cmd.CommandText = "declare @identity int;exec @identity = [dbo].[procname];select @identity"
Dim identity as int32 = cmd.ExecuteScalar

• ipisors 92539

  SSC-Addicted

  Points: 445

  More actions

  October 1, 2018 at 3:22 pm

  #2007612

  Thank you.
  I currently get an SSIS error (pasted below), immediately after my Msgbox raises (and prior to the second Msgbox in the below code)
  ERROR:
  at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
   at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
   at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
   at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

  CODE: (error occurs after first MsgBox, and prior to second MsgBox), in my real code I have the correct "connectionname":

     MsgBox("Next line begins declaration of connections, etc")
     Dim mConn As OleDb.OleDbConnection = DirectCast(Dts.Connections("connectionname").AcquireConnection(Nothing), OleDb.OleDbConnection)
     Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand()
     cmd.Connection = mConn
     cmd.CommandType = CommandType.StoredProcedure
     MsgBox("Next line is beginning of text stream open")

• ipisors 92539

  SSC-Addicted

  Points: 445

  More actions

  October 1, 2018 at 3:24 pm

  #2007613

  (Note - my connection manager pictured below)
  

• ipisors 92539

  SSC-Addicted

  Points: 445

  More actions

  October 1, 2018 at 3:26 pm

  #2007614

  I should also say that my OLE DB connection's actual name is ... well, I left it the way it defaulted to, which was:  server.database    
  ..Is that OK?  

  (so in my real code, instead of "connectionname", I have the name of the OLEDB connection manager, which happens to be, "server.database")