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

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

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