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...

If you check the All Executions report, does that give you any additional warnings or errors?

Also, if your Excel files always have column headings, importing the data with IMEX=1 and...

I am working with a script to exclude certain data types, and I was wondering if there is a comprehensive listing of the system.<datatype> for each of the mssql...

If you import the data into a table using all of the available outputs, is the data you require imported as part of that (contained in one of the imported...

This might be of interest:

https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs

I think that the temp table is scoped to the subprocess spawned by the EXEC statement.

If you concat the INSERT and SELECT statements to your @sql variable, with semicolon delimiters,...

Is this a SQL Server question? Not sure I follow.

I've now tried migrating from SSIS to Data Factory twice. The first time (three years ago) failed because DF could not do the things we needed. The second time was...

Hi Phil - this is adding an ID of zero into the 'unioned' result - I need the second part of this union to effectively inherit the ID from...

No problem. Just create a fake matching column in the STRUCTURE dataset (make sure its datatype matches what's in SOURCE)

SELECT Cols, Id
from Source 
UNION ALL
Select Cols, Id...

SELECT cols FROM TableA
UNION ALL 
SELECT Cols FROM TableB b 
WHERE NOT EXISTS (SELECT 1 FROM TableA a WHERE a.START = b.START)