SSIS - how to write a sql logic like stored pro. in object <execute SQL task editor>

nononame2021 256 Reputation points
2022-06-29T08:42:27.31+00:00

I would like to select datetime filed from table A, if it has value or not null & not empty, then delete data from table A.

can i write this logic in sql execution task directly? how to declear variable @hasvalue=select .....?

if variable has value then .... else .....

any sample for me to reference.

my sql in execution sql task (direct input) as below:

declare @apl varchar(100);
set @apl =(select top 1 mytable_timestamp from [dbo].[table_2])
if isnull(@apl ,'')<>'' then
delete from [dbo].[table_1] ;
insert into [table_1] select * from [table_2];
else
delete from [dbo].[table_2] ;

i got error as below:

[Execute SQL Task] Error: Executing the query "declare @apl varchar(100);
set @apl =(select top ..." failed with the following error: "Incorrect syntax near the keyword 'else'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,643 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,690 questions
0 comments No comments
{count} votes

Accepted answer
  1. LiHong-MSFT 10,051 Reputation points
    2022-06-29T08:56:23.72+00:00

    Hi @nononame2021
    Maybe you just need a DELETE statement, like this:

    DELETE FROM table_A   
    WHERE LEN(DATETIME_Field) > 0  
    

    Best regards,
    LiHong

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 116.9K Reputation points MVP
    2022-06-29T21:53:08.023+00:00

    declare @apl varchar(100);
    set @apl =(select top 1 mytable_timestamp from [dbo].[table_2])
    if isnull(@apl ,'')<>'' then
    delete from [dbo].[table_1] ;
    insert into [table_1] select from [table_2];
    else
    delete from [dbo].[table_2]

    Syntactically you are missing a BEGIN:END here. And you are having a THEN too many

       declare @date varchar(100);  
       set @date=(select top 1 mytable_timestamp from [dbo].[table_2])  
       if isnull(@date,'')<>''   
       BEGIN  
          delete from [dbo].[table_1] ;  
          insert into [table_1] select from [table_2];  
       END  
       else  
          delete from [dbo].[table_2]  
    

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.