Replace a Cursor to For Each loop for SSIS package

Question

Hi all,
I'm new to SSIS, currently I facing issue when create a SSIS Package by using Cursor.
Here with the Cursor I written

   use TestDB  
         
       DECLARE @storedprocedure_name varchar(100)  
       DECLARE @cmd VARCHAR(4000)  
         
       DECLARE @FilePath varchar(100)  
       DECLARE @FileNames varchar(100)  
         
       DECLARE spCursor CURSOR FAST_FORWARD FOR   
       SELECT CommandText,FilePath,FileNames FROM dbo.MultipleQuery  
       OPEN spCursor  
       FETCH NEXT FROM spCursor INTO @storedprocedure_name ,@FilePath,@FileNames  
       WHILE @@fetch_status = 0  
       BEGIN  
         
        SET @cmd = 'bcp "' +  
         @storedprocedure_name + '" queryout "' + @FilePath +@FileNames +'" -c -UTF8 -T -S'+@@SERVERNAME  
       PRINT @cmd  
         
         
       EXEC master..xp_cmdshell @cmd  
         
        FETCH NEXT FROM spCursor INTO @storedprocedure_name,@FilePath,@FileNames  
       END  
       CLOSE spCursor  
       DEALLOCATE spCursor  

Here with my sample DB,

Any suggestion to replace the Cursor in SSIS Package?

Answer 1

Hi @Eng Soon Cheah ,

We can use Execute SQL Task to execute the sql query in ssis package.

Hope the following link and picture will be helpful:

Replacing a SQL Cursor with SSIS

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.
Hot issues in November--What can I do if my transaction log is full?
Hot issues in November--How to convert Profiler trace into a SQL Server table?