Can you mock up two or three rows of source data, along with an idea of what the 'parsed' results will look like?

You mention having XML stored in SQL Server,...

Migrating your SSRS reports to Power BI Paginated reports is straightforward & sounds like a better solution to me. Or are you staying away from Power BI?

Not directly related, but if you migrate to VS2022, everything is 64-bit, putting an end to this kind of faffing about!

There certainly is, and it's called Source Control. You may have heard of 'Git' – that is one of the most popular source control tools in use right now.

Take a...

Mine also have '8' and they all seem to be working fine, so I think you're all set.

Thanks. Yes, that is the plan of course. I was just wondering if it was even necessary - could we rely on some setting external to the database to...

You've been here long enough to know that the protocol is to leave your original question intact and instead to post a response describing the resolution, in case this is...

Maybe have a setting somewhere which indicates whether to use UK, Australia or ROI.

Create a UDF to do the formatting which uses the environment setting. Change your code to use...

OK, that is surprising.

What sort of connection are you using (ADO/OLEDB etc)? Can you supply a (redacted as needed) connection string that is failing?

There are no error messages in the All Execution report. I created a table that gets populated when there are any errors when job gets executed. This is where...

DROP TABLE IF EXISTS #ToSplit;

CREATE TABLE #ToSplit
(
    SplitCol VARCHAR(8000) NOT NULL
);

INSERT #ToSplit
(
    SplitCol
)
VALUES
('test1,test2,test3,test4')
,('test5,test6');

SELECT ts.SplitCol
      ,ss.value
FROM #ToSplit...

The All Executions report is way more verbose than that.

Take a look in the All Executions report and see whether you get a more detailed error there.

Does it run if you execute it in T-SQL?

DECLARE @execution_id BIGINT;
 
EXEC SSISDB.catalog.create_execution
    @package_name = N'package.dtsx'
,   @execution_id = @execution_id OUTPUT
,  ...

In case it's useful, here's an example of handling this in T-SQL. Note that I had to remove the XML encoding, as that was giving an error.

DECLARE...