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:
HasExpired == TRUE
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?
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.
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
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.
Make sense?