ForEach Loop utilisation

Hello,

ForEach Loop Item allow to make operations row per row.

How can i do operations 10 rows per 10 rows or 100 rows per 100 rows ?

Thanks !So, a few folks have blogged about this.

http://www.sqlis.com/default.aspx?59
http://sqljunkies.com/WebLog/knight_reign/archive/2005/03/25/9588.aspx

I thought Jamie did, but a quick search turned up empty.
In any case, one of the best sources of information on SSIS is Jamie Thomson's blog.
http://blogs.conchango.com/jamiethomson/default.aspx

HTH|||

Coroebus wrote:

How can i do operations 10 rows per 10 rows or 100 rows per 100 rows ?

Can you elaborate on exactly what you want to do? "10 rows per 10 rows" isn't very descriptive.

-Jamie|||

I want to make a request to yahoo stock quotes.

In my database i have a list of quotes, and i want to get a page with a selection of quotes.

look this example :
http://fr.old.finance.yahoo.com/d/quotes.csv?s=SLB.PA&f=snl1d1t1c1ohgv&e=.txt

I can request up to 200 quotes maximum like this

http://fr.old.finance.yahoo.com/d/quotes.csv?s=ATO.PA,AF.PA,AC.PA,ADE.PA&f=snl1d1t1c1ohgv&e=.txt

In my package i want to be able to create those request with a defined list of quotes.

I hope you can understand my bad english...

thanks a lot

|||Your english is good, don't worry about that :)

You can read your list of quotes into an Object variable using 1 of 2 methods:
1) Use the Execute SQL Task or
2) Use a data-flow with a Recordset destination component.

Once there you can loop over it using the Foreach Loop's "Foreach ADO Enumerator" and put the stock into a variable. The variable can then be used in a property expression to build a URL ("http://finance.yahoo.com/q?s=" + User::VariableName) for the HTTP Connection Manager that will retrieve the stock quote from the Yahoo site.

I don't have an SSIS instance to hand so can't build a demo of this but if you're having trouble let me know and I'll see what I can do later.

In the meantime, this article at SQLIS.com explains the basic process that you need to go through here: http://www.sqlis.com/default.aspx?59

Hope this helps.

-Jamie|||Ok, but how can I make row with 200 quotes ?|||

Coroebus wrote:

Ok, but how can I make row with 200 quotes ?

I'm not quite sure I understand. Do you mean that instead of 200 rows with 1 quote in each you want 1 row containing the same 200 quotes? [In other words you want to pivot the data.]

-Jamie|||No, I have in a table a list of 2000 quotes. I had to treat it by 200 quotes Items, request yahoo with this kind of http request.
In a sense, my package schould do this :

1- Create the list of quotes
2- Create http request for the 200 first quotes
3- Treat the http file
4- Create http request for the 200 Next quotes
5- Treat the http file
6- ...

I can do it quotes per quotes but i think it is more efficient 200 per 200

Thanks a lot for your help|||So you want to send the 2000 stocks to Yahoo in batches of 200, is that correct?
And the 200 stocks are stored in a table, is that correct?

If so I would do the following:
1) Have a Foreach loop that pulls a batch of 200 out of the table. It does this using the new Yukon windowing functions (http://sqljunkies.com/HowTo/4E65FA2D-F1FE-4C29-BF4F-543AB384AFBB.scuk). Each time around the loop it pulls out the next batch of 200 until there is none left.
2) Inside that Foreach Loop have another Foreach loop that loops over the 200 returned rows, sending the request through to Yahoo for each one.

Does that make sense? Have I understood you correctly?

-Jamie|||YES !!!!

I was sure you'll find wht i want !

thanks a lot|||No problem. Let us know how you get on. The windowing functions are right up there on my "favourite new features of SQL Server" list. Nowhere near SSIS of course

-Jamie|||

Sorry... Those functions looks nice but are not made to my problem...

I'm trying to resolve my problem with a transformation script to make a recordset containing rows in the correct format.

If you want, i'll send you the script

Nico

|||If you think it'll help. jamie.thomson[at]donotspamme.conchango.com

-Jamie