1

I have a SSIS package that is being developed in Visual Studio 2022 targeting SQL Server 2019 and I have a weird problem.

I have a data flow that refreshes a bearer token meaning it retrieves the token from the database, check the expiry and if it is expired, it refreshes it and if not, it reuses the token from the database. It then stores that token in a package scoped variable to use later in other data flows.

To update the token or reuse the token, I use two different script components that is routed through a conditional split with the following expressions:

  • Output: "Token Expired", Expression: HasExpired == TRUE
  • Output: "Token NOT Expired", Expression: HasExpired == FALSE

The problem I am having is: My token is not expired but for some reason the void PostExecute()in the script component that is responsible for renewing expired tokens is being triggered. I did plenty of tests and it seems it works sometimes when I restart VS but it starts doing that again after few runs.

An idea what may be causing this?

See SSIS data flow

I tried setting break points in the script components but the incorrect method still kept triggering. I also tried enabling data viewer and the data viewer shows the correct data but the wrong PostExecute() still triggers.

1 Answer 1

1

You can build out a simple reproduction that does shows the conditional split is probably doing what it should be doing

Here I generate 2 rows, with the bit set to true and false and see the correct path lighting up. And then I make both False and zero rows route to the Row Count component and it gets the green check as does the Script component

enter image description here

If you look down to the Output, you'll see a line that says

Information: 0x0 at DFT Demo, PostExecute of Expired path: RowsReceived = False

While you don't show the code for your PostExecute, I'm willing to post an answer with an edumacated guess. The PostExecute event is going to fire every time, whether there was any data to light up the component or not. This is very common in SSIS stuff as it needs to do cleanup after itself so logic you have in your Script Component is going to fire every time, unless you tell it not to.

In the following script task I declare a member variable called rowsReceived and initialize it to False.

In my PostExecute method, I write to the output log twice. The first is the message you see in my screenshot. The second, inside the If block will only fire in the event that row actually landed in the component.

The final and most trivial piece is the ProcessInputRows method. Whether 1 row or a billion, they'll all evaluate the same in that they flip our rowsReceived from False to True.

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    bool rowsReceived;

    public override void PreExecute()
    {
        base.PreExecute();
        rowsReceived = false;
    }

    public override void PostExecute()
    {
        base.PostExecute();
        bool fireAgain = false;
        ComponentMetaData.FireInformation(0, "PostExecute of Expired path", string.Format("RowsReceived = {0}", rowsReceived), "", 0, ref fireAgain);
        if (rowsReceived)
        {
            ComponentMetaData.FireInformation(0, "PostExecute of Expired path", "DO TASK HERE TO REFRESH TOKENS", "", 0, ref fireAgain);
        }
    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        rowsReceived = true;
    }

}

Switching my source query back to 1 false, 1 true, you can see a new message showing up in the Output log that corresponds to placeholder where you would perform token refresh activities.

enter image description here

Make sense?

Sign up to request clarification or add additional context in comments.

1 Comment

You are a genius! I had no idea PostExecute events were always fired regardless of if data passed through the ProcessInputRows methods. Thank you. That was exactly my problem. I tested it and only one PostExecute fires now

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.