1

I changed an Execute SQL Task targeting my default, local SQL Server instance from OLE DB to ODBC. The SQL is

delete from tablename

The ODBC version works fine when the table has records but fails when the table is empty.

I thought about using the result set of another SQL Task to populate a variable but that would not work. Reading this SO article Delete statement fails when called from SSIS identified the source of the problem (ODBC version), but it still didn't provide the answer. Other web articles suggested no workaround.

Are there any other methods or approaches to conditionally call the delete only if the table is not empty?.

Hadi
  • 36,233
  • 13
  • 65
  • 124
stumit63
  • 61
  • 6

1 Answers1

0

It occurred to me to just use T-SQL to solve the problem. I used a T-SQL conditional statement which works fine. The ODBC SQL Task succeeds whether or not the table is empty.

if 0 < (select count(1) from tablename)
delete from tablename
stumit63
  • 61
  • 6
  • I also think the IF clause is a solution to the other SO question I referenced in my question but I don't yet have the reputation to answer or comment on that one. – stumit63 Jan 31 '22 at 01:04
  • I upvoted your questions. Feel free to add the answer to the other SO question. Also, don't forget to mark your answer as accepted. Good Luck – Hadi Jan 31 '22 at 17:52
  • 1
    In case your data is huge, executing `select count(1)` may affect your server performance. Why not add a dummy `SELECT 1` after the `Delete` command? – Hadi Jan 31 '22 at 17:55
  • @Hadi I'll edit the answer and put it in context. Your comment indicated a missed premise in the answer and that I need to show how it is a solution for the other SO question. Thanks. – stumit63 Feb 01 '22 at 21:36