Ormlite : Execute 2 stored procedures in one go with out parameter

Question

I need to run following sql from https://learn.microsoft.com/en-us/sql/integration-services/ssis-quickstart-run-tsql-vscode?view=sql-server-ver15

it executes 2 stored procedures with one out parameter

Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx',
    @execution_id=@execution_id OUTPUT,
    @folder_name=N'Deployed Projects',
      @project_name=N'Integration Services Project1',
    @use32bitruntime=False,
      @reference_id=Null
Select @execution_id
DECLARE @var0 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,
    @object_type=50,
      @parameter_name=N'LOGGING_LEVEL',
      @parameter_value=@var0
EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO

I want to run it using ormlite as its already used in project. I tried using db.ExecuteSql() as it contains executing 2 stored procedures and first using seconds output as input but I don't understand how to get the out parameter (execution_id) value back? or is there any other way to do it using ormlite only.

db.ExecuteSql(@"Declare @execution_id bigint
    EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx',
        @execution_id=@execution_id OUTPUT,
        @folder_name=N'Deployed Projects',
          @project_name=N'Integration Services Project1',
        @use32bitruntime=False,
          @reference_id=Null
    Select @execution_id
    DECLARE @var0 smallint = 1
    EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,
        @object_type=50,
          @parameter_name=N'LOGGING_LEVEL',
          @parameter_value=@var0
    EXEC [SSISDB].[catalog].[start_execution] @execution_id
    GO")

How to get execution_id back?

Thanks.

Answer 1

I don't know anything about ormlite but the documentation for "Issuing raw Queries" looks like what you need. Also, does

There is no OUT parameter to be dealt with. The results of execution are a result set which has 1 row and 1 column, sometimes referenced as scalar results.

ORMLite which appears to be java based

// I am too lazy to lookup multi-line strings in Java
GenericRawResults<String[]> rawResults =
  orderDao.queryRaw(
    "Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx',
    @execution_id=@execution_id OUTPUT,
    @folder_name=N'Deployed Projects',
      @project_name=N'Integration Services Project1',
    @use32bitruntime=False,
      @reference_id=Null;
// This is what you're calling an OUT parameter but it's just a result set
Select @execution_id;

DECLARE @var0 smallint = 1;
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,
    @object_type=50,
      @parameter_name=N'LOGGING_LEVEL',
      @parameter_value=@var0
EXEC [SSISDB].[catalog].[start_execution] @execution_id;");
// there should be 1 result
List<String[]> results = rawResults.getResults();
// the results array should have 1 value
String[] resultArray = results.get(0);
// this should print the number of orders that have this account-id
System.out.println("The execution_id is  " + resultArray[0]);

ormlite-servicestack is .NET product so there you're looking to run a query, the Custom SQL documentation seems appropriate.

string query = @"Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx',
    @execution_id=@execution_id OUTPUT,
    @folder_name=N'Deployed Projects',
      @project_name=N'Integration Services Project1',
    @use32bitruntime=False,
      @reference_id=Null;
// This is what you're calling an OUT parameter but it's just a result set
Select @execution_id;

DECLARE @var0 smallint = 1;
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,
    @object_type=50,
      @parameter_name=N'LOGGING_LEVEL',
      @parameter_value=@var0
EXEC [SSISDB].[catalog].[start_execution] @execution_id;";
// a bigint for the results
long executionId = 0l;

executionId = db.SqlScalar<long>(query);