Looping through Foreach loop on monthly basis

Question

Post reply

Looping through Foreach loop on monthly basis

Shadab Shah

SSCarpal Tunnel

Points: 4759
More actions
December 23, 2013 at 6:07 am

#302580

Hi all,
I am trying to loop through the ForEach Loop Container,on monthly basis in SSIS.
I was not able to find any Loop editor which could do this job for me.
Any Advice or link which could guide me through it.
Thanks .

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Umer Akram SSC-Insane Points: 22209 More actions · Answer 1

please share more detail if possible share the sample data to help us understand the issue. so that we can answer 🙂

Lowell SSC Guru Points: 323546 More actions · Answer 2

well, SQL requires a paridigm shift here: you process everything in a set, and not loops;

there's a phrase in SQL called RBAR (Ree-bar) meaning Row By Agonizing Row, which is a remider that loops hurt the thought processes of a set based mindset.

while SQL server certainly has cursors and WHILE loops to do looping processing, if you gave us an example of what you are doing inside the loop, we can help you replace that loop with a SET based operation that is at least one or two orders of magnitude faster than any loop.

show us some code, and we'll tweak it ofr you.

however, since i like to provide code examples in every post possible,for reference, here's two loop examples:

--cursor

USE MASTER

declare

@isql varchar(2000),

@dbname varchar(64)

declare c1 cursor for select name from master..sysdatabases where name not in ('master','model','msdb','tempdb')

open c1

fetch next from c1 into @dbname

While @@fetch_status <> -1

begin

select @isql = 'ALTER DATABASE @dbname SET AUTO_CLOSE OFF'

select @isql = replace(@isql,'@dbname',@dbname)

print @isql

exec(@isql)

select @isql = 'ALTER DATABASE @dbname SET RECOVERY SIMPLE'

select @isql = replace(@isql,'@dbname',@dbname)

print @isql

exec(@isql)

select @isql='USE @dbname checkpoint'

select @isql = replace(@isql,'@dbname',@dbname)

print @isql

exec(@isql)

fetch next from c1 into @dbname

end

close c1

deallocate c1

GO

a while loop

create table Numbers (Num int)

declare @i int

SET @i=1

while @i < 10000

begin

INSERT INTO dbo.NUMBERS(NUM) VALUES(@i)

SET @i = @i + 1

end

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!