Looping thru files in a folder-stop after x files

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Looping thru files in a folder-stop after x files

Author

Topic

sarahmfr
Posting Yak Master

214 Posts

Posted - 2011-09-05 : 13:07:37

I am using for each loop container to go thru files in a folder

I have 1000s of files in the folder.

I want to process only 500 at a time

how can i tell the package to stop after processing only 500 files at a time

Thanks

sarah

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-05 : 13:58:53

you need to create a counter variable in ssis package and increment it inside loop. then check for condition variable <=100 in precendence constraint of dummy sql task added as first task inside loop by choosing expression and constraint option.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sarahmfr
Posting Yak Master

214 Posts

Posted - 2011-09-05 : 14:08:03

Thanks

sarah

sarahmfr
Posting Yak Master

214 Posts

Posted - 2011-09-05 : 18:56:58

I created the counter
but now inside the loop it executes the script that checks the counter on and on after the counter exceeded 40
It does not do the other tasks but that script is kept being executed
Appreciate your help

sarah

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-09-06 : 03:13:42

If I remember correctly the for-each loop container will loop through all files satisfying the filename filter criteria. What I would do is probably to add an Execute SQL task with a query like this:

create table #tmpTable (id int identity(1, 1), filename varchar(200))
INSERT INTO #tmpTable (filename)
EXEC xp_cmdshell 'dir d:\folder\*.dat' /b'
select top 40 filename from #tmpTable --> where...
drop table #tmpTable

Then add filename as a ResultSet and map the full result set to a user variable. Then in the for-each loop container loop through the variable collection. I think this should be doable but it might take some tweaking. I've never tried this myself.

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

sarahmfr
Posting Yak Master

214 Posts

Posted - 2011-09-06 : 22:39:51

Thanks

sarah

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources