How can this accomplished? - Paging alternatives?

Question

How can this accomplished? - Paging alternatives?

robsworld2006

Grasshopper

Points: 13
More actions
February 5, 2008 at 12:04 am

#123475

Hi there,
I'm fairly new to SSIS but essentially I would like to create the following:
Table Structure:
===========
Code Name
ABC Abc Industries
..... .......
XYZ Xyz Industries
1. Read an excel file into the database (I have this working)
2. Loop through the result set and concatinate the [Code] to a variable
3. Every 200 rows I would like to use the varible that contains 200 codes e.g. Myvar = ABC,ABC1,ABB1,ABB2...... into another task, the variable would be reset after the task has been complete for the next 200 rows
I have no idea how I can perform the kind of paging that I am after, I know the Foreach control loops through each record and I could use Scriptmain to attach to the variable but I'm not sure how to perform an additional task every 200.
Thanks in advance
Regards Rob

Viewing 5 posts - 1 through 4 (of 4 total)

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

Alexander G. SSCrazy Points: 2705 More actions · Answer 1

What you want is an aggregation of data. Except from using the common aggregations (max, count, ...) IMHO this is not the main scope of ETL.

What I recommend is coding a CLR aggregation function to concatenate strings (it's easier than it sounds).

Then generate a grouping value using row_number like

SELECT code, name, ROW_NUMBER() OVER(ORDER BY Code) / 200 AS groupnr

and aggregate the codes grouping by groupnr.

robsworld2006 Grasshopper Points: 13 More actions · Answer 2

Thanks you for your response I appreciate it... I will have to look into this, I think I found what I should be doing thanks to your post:

http://www.code-magazine.com/Article.aspx?quickid=990712132

Could you please write a bit more detail to how I might go about creating what I need, say I did create an aggregate function called 'CONCATMYCODES' how do I go about using it in a query?

SELECT CONCATMYCODES(Code), ROW_NUMBER() OVER(ORDER BY Code) / 200 AS groupnr

FROM MyTable

GROUP by groupnr <---- this gives an error (Do I have to select into a temp table first?)

would that output as:

AAA,BBB,CCC..... (200 items)

XXX,YYY,ZZZ.... (200 items

etc ?

As you can see I'm very new to all of this so any help would be appreciated thanks

Regards Robsworld

Alexander G. SSCrazy Points: 2705 More actions · Answer 3

Two thinks to mention I think:

First you can't use the alias in the group by clause, just repeat the whole expression there. Second, you might have to qualify your user defined aggregate:

SELECT dbo.CONCATMYCODES(Code), ROW_NUMBER() OVER(ORDER BY Code) / 200 AS groupnr

FROM MyTable

GROUP by ROW_NUMBER() OVER(ORDER BY Code) / 200

That should do it.

robsworld2006 Grasshopper Points: 13 More actions · Answer 4

Thanks again... but for

GROUP by ROW_NUMBER() OVER(ORDER BY Code) / 200

According to this website scroll to the bottom of the page:

An error will occur:

Windowed functions can only appear in the SELECT or ORDER BY clauses.

The article suggest to store the results in a table I'll give it a go

Regards Rob