| Author |
Topic |
|
bondwiththebest
Starting Member
46 Posts |
 Posted - 2009-03-03 : 09:44:38
|
| Is there a better way to do this??I have n rows from a file. Now I would like to take only 200 samples every time. I thought of getting row count and dividing by 200 and get the samples. Is there any other way of doing it in SSIS??? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-03-03 : 09:46:56
|
COUNT / 200 will give you 0 records for files containing 199 records or less.And only 5 sample records for a file with 1000 records.I think you meanSELECT TOP 200 * FROM file
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-03 : 10:06:59
|
quote:
Originally posted by bondwiththebest
Is there a better way to do this??I have n rows from a file. Now I would like to take only 200 samples every time. I thought of getting row count and dividing by 200 and get the samples. Is there any other way of doing it in SSIS???
Add a row number transformation after flat file source and then use rownumber value to iterate 200 rows at a time. use a for each loop container for looping |
|
|
|
bondwiththebest
Starting Member
46 Posts |
Posted - 2009-03-03 : 10:09:29
|
| ya I dint exactly mean just by dividing by 200. But I need 200 samples through the file irrespective of the file length. If the file has 8k rows, I need 200 samples, If a file has 5k, I need 200 samples. I don't have files which are less than 200 rows, So i should be fine. |
|
|
|
bondwiththebest
Starting Member
46 Posts |
Posted - 2009-03-03 : 10:17:34
|
| can you tell me what do u mean by row number transformation? How do i use control flow task in data flow ? I mean I can srcipt it out but it is going to be row by row sampling. Is there any other way! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-03 : 12:47:26
|
quote:
Originally posted by bondwiththebest
can you tell me what do u mean by row number transformation? How do i use control flow task in data flow ? I mean I can srcipt it out but it is going to be row by row sampling. Is there any other way!
see thishttp://www.sqlis.com/post/Row-Number-Transformation.aspxyou need to use it in data flow just after the flat file source task.i didnt understand what you mean by row by row sampling. arent you interested in 200 rows at a time? |
|
|
|
bondwiththebest
Starting Member
46 Posts |
Posted - 2009-03-03 : 13:23:58
|
| That looks great, I will see if that works for me. I need 200 samples from a file which as n rows. Generally n is like 500 rows or above. I dont need the top 200 from file. I need the 200 rows from the file....spread evenly(apprx) |
|
|
|
bondwiththebest
Starting Member
46 Posts |
Posted - 2009-03-03 : 13:52:34
|
| I cannot install a third party software in my server. is there any other way???? |
|
|
|
bondwiththebest
Starting Member
46 Posts |
Posted - 2009-03-03 : 15:47:22
|
| I was able to add an identity col using scripting and I have the total no of rows in that file....Right now I am stuck at getting only 200 samples from file. I couldnt figure out the condition in "condiitonal split"I use something like ID<=( ID +(TotalRows/200)) but that gives all the rows. Is there any better way?? |
|
|
|
|
Row sampling