Its strange really because whether its runs on SSMS or SSIS, its will operate on the SQL SERVER level. so time should not be the different. if the query you mentioned above is causing the performance issue this should definitely have performance issue when you run it SSMS as well. Cursor do not seems to be a big issue as you are using is to generate dynamic sql.
if you really want to use the SSIS, then implement the logic inside SSIS. But 1st let Adjust your query from the cursors
SELECT ts.object_id,
'Delete From ' + ts.name + ' Where ' + x.colQuery AS DeleteQuery
FROM sys.tables ts
CROSS APPLY
(
select STUFF((SELECT 'AND ' + QuoteName(c.name) + ' is null '
FROM sys.all_columns c
WHERE c.object_id = ts.object_id
AND c.name != 'ID'
AND c.name != 'INDEX0'
AND (c.name NOT LIKE 'MAS%' AND c.name NOT LIKE 'Z%')
For xml path ('')
),1,3,'') AS colquery
) X
WHERE ts.object_id IN (
SELECT ac.object_id
FROM sys.all_columns ac
WHERE ac.name = 'INDEX0'
)
ORDER BY ts.name
2nd part, in which you delete from the table by using While loop (Row by row). not good its will take ages to finish. to avoid this
delete data in chunks. so if merge both logic the final query will look like this
Declare @vTop as int = 25000----------- Need to determine which value will perform better
Declare @1stQuery as varchar(500) = ''
Declare @LastQuery as varchar(500) = ''
select
@1stQuery =
' DECLARE @r INT;
SET @r = 1;
WHILE @r > 0
BEGIN
BEGIN TRANSACTION;
DELETE TOP ('+ CAST(@vTop as varchar(10)) +')
FROM '------------- Divided the query in parts
, @LastQuery =
' SET @r = @@ROWCOUNT;
COMMIT TRANSACTION;
-- CHECKPOINT; -- if Recovert Model simple
-- BACKUP LOG ... -- if Recovert Model full
END'
SELECT ts.object_id,
@1stQuery + ts.name + ' Where ' + x.colQuery + Char(10) + @LastQuery AS DeleteQuery
FROM sys.tables ts
CROSS APPLY
(
SELECT STUFF((SELECT 'AND ' + QuoteName(c.name) + ' is null '
FROM sys.all_columns c
WHERE c.object_id = ts.object_id
AND c.name != 'ID'
AND c.name != 'INDEX0'
AND (c.name NOT LIKE 'MAS%' AND c.name NOT LIKE 'Z%')
For xml path ('')
),1,3,'') AS colquery
) X
WHERE ts.object_id IN (
SELECT ac.object_id
FROM sys.all_columns ac
WHERE ac.name = 'INDEX0'
)
ORDER BY ts.name
its your choice now whether you want to run in a complete batch or you want to run that query one by one. you can do it in SSIS easy.