Execute SSIS (package) in SSMS using T-SQL and return a value

Question

Using VS (2019) I created an Integration Services Project to code an SSIS package. The goal was to call a Web API from an SSIS package using input variables like username/password/url and so on. I successfully achieved this using Script Task and Dts.Variables and F5/Run to see successful results. What I want to achieve now is to return (like an out parameter) an Id returned to me by the Web API. I want to have this Id returned in the SSMS Results window when I execute the SSIS script in SSMS.

Here is what I have done:

1. VS > Open dtsx file in Designer and in the "Control Flow" tab, right clicked to add variable:

2. Right clicked the Script Task I had created and added the variable created above in the ReadWriteVariables section:

3. Using "Edit Script" to code the Web API call and return a value and set it as follows:

//MessageBox.Show(id);  
Dts.Variables["User::Id"].Value = Convert.ToInt32(id);

The MessageBox was just to test if the id is returned by the Web API and I get an alert of the "id" when I Run/F5 the SSIS project in Visual Studio. The second line is where I have set the Variable.

4. I execute the SSIS in using T-SQL in SSMS from an example here:

Declare @execution_id bigint  
  
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'ssisWAP.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'SSIFolder', @project_name=N'SampleProject', @use32bitruntime=False, @reference_id=Null
 
Select @execution_id

DECLARE @var0 sql_variant = N'username'

EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=20, @parameter_name=N'Username', @parameter_value=@var0

DECLARE @var1 sql_variant = N'password'

EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=20, @parameter_name=N'Password', @parameter_value=@var1

EXEC [SSISDB].[catalog].[start_execution] @execution_id

The above executes properly, calling the API and an out @execution_id is shown in Result window. But no sign of the variables User::Id I created earlier. Is it possible to show that value in the Result window? If not, is there a way to see that the data in that variable in SSMS after SSIS execution (even from designer)?

I already had a look at this Stack Overflow Q & A where the solution is to use "Execute SQL Task" and "pass" the variable into a procedure. This I guess is one way to do it but I want to just get the Id in the return when I execute the script as shown above.

Answer 1

Fun question!

When you hit F5/Debug within Visual Studio, that spins up a special environment to allow for breakpoints, debugging and graphical interaction (MessageBox). That can't happen when the package runs on the server because who's going to log into the server and hit the [Ok] button on that message box? DTS, the precursor to SSIS, did allow you to have message boxes and yes, I've logged onto servers only to be assailed by all these popups...

Instead, when the SSIS package is running in unattended mode, if you left code in there that interacts with the UI, the package run-time will throw an exception. Instead, what we have from a command line execution perspective, is what you see in the Output window. The Results window is a graphical representation of package execution progression. It's fine, but you can't copy from there and it truncates messages. Instead, click to the Output window to see and copy all the information.

How do things "get" to the Output window? Events. In your code, instead of writing MesssageBox.Show(id); raise events instead.

bool fireAgain = false;
Dts.Events.FireInformation(0, "ID results", id.ToString(), "", 0, ref fireAgain);

Now when you run your packages, assuming you request that Information events are reported

dtexec.exe /file foo.dtsx /rep eiw

you will see the value in your run log. This covers Visual Studio execution, but what about the SSISDB? That's a different animal.

The SSISDB captures all the output information from SSIS packages and stores it into its own structure. I tech reviewed Tim Mitchell's book "The SSIS Catalog: Install, Manage, Secure, And Monitor Your Enterprise ETL Infrastructure" and he does an excellent job covering the catalog's structure and how to use it. (I get no compensation for mentioning the book).

In the above TSQL, you will see that creating an instance of execution generates a bigint that is assigned into @execution_id. That is the key to the kingdom. Assuming the value is 531463, the following script would tease out the value I fired off in the Information event above

DECLARE @execution_id bigint = 531463;

SELECT
    -- Script Task:Information: 42
    OM.message
,   *
FROM
    SSISDB.catalog.operation_messages AS OM
WHERE
    OM.operation_id = @execution_id
    AND OM.message_type = 70 /*Information*/ ;


SELECT
    -- Script Task:Information: 42
    EM.message
    ,   EM.*
FROM
    SSISDB.catalog.event_messages AS EM
WHERE
    EM.operation_id = @execution_id
    AND EM.message_type = 70 /*Information*/ ;

event_messages and operation_messages are generally the same data, just in different views but you'd see the message value in an information message type (70) is recorded as Script Task:Information: 42 which shows the 42 which was my id. If your SQL Server supports STRING_SPLIT, it's a simple modification to extract the value itself

DECLARE @execution_id bigint = 531463;

SELECT
    -- Script Task:Information: 42
    OM.message
,   D.value
,   *
FROM
    SSISDB.catalog.operation_messages AS OM
    CROSS APPLY
(
    SELECT
        *
    FROM
    (
        SELECT
            SS.*
        ,   ROW_NUMBER() OVER (ORDER BY(SELECT NULL)) AS row_num
        FROM
            STRING_SPLIT(OM.message, ':') AS SS
    ) AS SSO
    WHERE
        -- skip the first two elements as they are Script Task and Information
        SSO.row_num > 2
) AS D
WHERE
    OM.operation_id = @execution_id
    AND OM.message_type = 70 /*Information*/ ;

Finally, you could always control your own destiny by having a custom table for storing these values.

CREATE TABLE dbo.PackageRunLog
(
    PackageName nvarchar(255) NOT NULL
,   ExecutionId bigint NOT NULL
,   ExecutionDate datetime2(0) NOT NULL
,   MyId int NOT NULL
);

Use an Execute SQL Task to populate that and the first 3 parameters would by System scoped variables from the SSIS package: System::PackageName, System::ServerExecutionID, System::StartTime. The final would then be @[User::Id] or whatever you needed.