I'm trying to delete (and update, but if I can delete than I'll be able to update) product data from MySQL website database using SSIS, when those products have been marked in our ERP (and in the sql server database used for reporting) as discontinued. I've tried the following:
First Attempt: Saving the rows-to-be-deleted to a recordset and using a for-each loop with an execute sql task to delete them as described here.
Result: Partially works, but is extremely slow and fails after about 500 deletes each time. Makes me wonder if the MySql database has some kind of hacker-protection feature.
Second Attempt: Converting the primary key for all rows-to-be-deleted into a comma-separated string variable using FOR XML PATH : as described here (or, rather, a series of them because of the 4000 char limit).
SQL Select Code (works fine)
WITH CTE (Product_sku,rownumber) AS
(
SELECT product_sku
, row_number() over(order by product_sku)
FROM product_updates
WHERE action = 'delete'
)
SELECT
Delete1= cast(
(SELECT TOP 1
STUFF(
(SELECT ',''' + product_sku+'''' FROM CTE
WHERE cte.RowNumber BETWEEN 1 and 700
FOR XML PATH (''))
, 1, 1, '') )
AS varchar(8000))
... and nine more of these select statements into additional variables to allow for larger delete operations.
And then using this result to delete records from MySql using an Execute SQL command with the following code:
DELETE FROM datarepo.product
WHERE product_sku in (?)
Result: The package executed but failed to delete anything. When viewing the MySql query log file I saw the following, which tells me why it failed to delete anything.
DELETE FROM datarepo.product
WHERE product_sku in ('\'')
Note that this same SSIS Execute SQL statement , when using hardcoded values (like the following), deletes just fine.
DELETE FROM datarepo.product
WHERE product_sku in ('1234','5678','abcd', etc...)
I haven't been able to find anything else online. As Reza Rad said in the first linked post, it's hard to find material about using SSIS to perform operations on MySql.
