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

Question

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 1

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 · Answer 2

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 · Answer 3

ipisors 92539

SSC-Addicted

Points: 445

October 1, 2018 at 3:24 pm

#2007613

(Note - my connection manager pictured below)

ipisors 92539 SSC-Addicted Points: 445 More actions · Answer 4

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")