Replace a Cursor to For Each loop for SSIS package

Eng Soon Cheah 81 Reputation points
2020-12-04T05:38:28.353+00:00

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,
45085-kdjje.png

Any suggestion to replace the Cursor in SSIS Package?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,596 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,449 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,544 questions
0 comments No comments
{count} votes

Accepted answer
  1. Monalv-MSFT 5,891 Reputation points
    2020-12-04T07:02:47.807+00:00

    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

    45048-est.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.
    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?

    0 comments No comments

0 additional answers

Sort by: Most helpful