How to take destination output as input for execute sql task in SQL

Bala Narasimha Challa 466 Reputation points
2021-03-03T16:53:03.21+00:00

Hi Team,
Am writing a SQL query to generate delete script as show in as shown in bellow.

DELETE FROM MyDB.dbo.VCardService WHERE MFRef =1000;
DELETE FROM MyDB.dbo.ScheduleChangeQueues WHERE MFRef =1000;
DELETE FROM MyDB.dbo.Riskified WHERE bookingRef =1000;
DELETE FROM MyDB.dbo.ReissuedBookings WHERE bookingRef =1000;
DELETE FROM MyDB.dbo.RefundRequest WHERE bookingRef =1000;

I want to take this script in SQL execute task and need to execute one by one entry.

Please help on this issue.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,577 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Monalv-MSFT 5,896 Reputation points
    2021-03-04T02:31:35.943+00:00

    Hi @Bala Narasimha Challa ,

    Please drag 5 Execute SQL Tasks and connect them.

    Then the Execute SQL Tasks will be executed by running the ssis package.

    74071-controlflow.png

    Best regards
    Mona

    ----------

    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  2. MelissaMa-MSFT 24,176 Reputation points
    2021-03-04T02:45:05.25+00:00

    Hi @Bala Narasimha Challa ,

    I want to take this script in SQL execute task and need to execute one by one entry.

    What does 'SQL execute task' refer? Would you like to execute this script in SSIS, maintenance plan or other tool?

    From TSQL side, you could refer below and check whether it is working.

    drop table if exists ##temp  
      
    declare @script nvarchar(max) = '  
    DELETE FROM MyDB.dbo.VCardService WHERE MFRef =1000;  
    DELETE FROM MyDB.dbo.ScheduleChangeQueues WHERE MFRef =1000;  
    DELETE FROM MyDB.dbo.Riskified WHERE bookingRef =1000;  
    DELETE FROM MyDB.dbo.ReissuedBookings WHERE bookingRef =1000;  
    DELETE FROM MyDB.dbo.RefundRequest WHERE bookingRef =1000;'  
      
    select IDENTITY(int,1,1) ID,*   
    into ##temp  
    from string_split(@script,';')  
    where isnull(value,'')<>''  
      
    declare @i int=1  
    declare @max int  
    declare @sql nvarchar(1000)=''  
    select @max=count(*) from ##temp   
      
    while @i<=@max  
    begin	  
    	select @sql=value from ##temp where id=@i  
    	BEGIN TRANSACTION  
    		execute sp_executesql  @sql--execute the delete statement	         
    	COMMIT TRANSACTION   
    	WAITFOR DELAY '00:00:01' --add some wait time if ncessary   
    	set @sql=''  
    	set @i =@i+1      
    end  
    

    If your SQL Server is pre-2016, you could refer below user-defined function instead of string_split.

    CREATE FUNCTION [dbo].[SplitString]  
    (  
        @List NVARCHAR(MAX),  
        @Delim VARCHAR(255)  
    )  
    RETURNS TABLE  
    AS  
        RETURN ( SELECT [Value] FROM   
          (   
            SELECT   
              [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],  
              CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))  
            FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)  
              FROM sys.all_objects) AS x  
              WHERE Number <= LEN(@List)  
              AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim  
          ) AS y  
        );  
    

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments