Question on Row Count Transformation

minority80

Hi all,

i'm using Row Count Transformation downloaded from http://www.sqlis.com/93.aspx to generate surrogate key.

in my data flow, i have OLE Data Source connected to Row Count Transformation connected to SCD.

i've configured ok and the package ran without error.

my problem is my surrogate key column won't generate the next increment number if i run the package the 2nd time and there is new row in the dimension, e.g.

Id BusinessKey BusinessName
--- ------------------ ---------------------
1 RDO Radio
2 MAG Magazine
3 TV Television
1 INT Internet } added new row from source

did i miss something please help. thanks.

jwelch

You need to set the Start Number (seed) to the max ID + 1 in your table, so that it starts incrementing at the proper key.

You might try using an Exec SQL in the control flow to store the max id in a variable, then set the seed through an expression on the data flow component.

Phil Brammer

I believe in the control flow, before the data flow, you will have to "select max(id) from table" and store that in a variable. Then in the data flow, you'll have to use expressions to set the starting number to that of the variable plus one.

Also, it's the "Row Number Transformation," not the "Row Count Transformation" just to be clear for those tuning in. SSIS comes with a "Row Count Transformation" built-in.

Phil Brammer

What John said above... I was typing while he was posting.

minority80

opss.... ya.... it's Row Number Transformation.. sorry for the mistake... thanks for the replies....

in this Row Number Transformation package, start number(seed) cannot be filled with expression... so can i say that this package can only be used to generate surrogate key if it's run only once

thanks again...

Phil Brammer

minority80 wrote:

opss.... ya.... it's Row Number Transformation.. sorry for the mistake... thanks for the replies.... in this Row Number Transformation package, start number(seed) cannot be filled with expression... so can i say that this package can only be used to generate surrogate key if it's run only once thanks again...

Right click on the transformation and select properties. In that window you will find the "Expressions" box. Expand that and go from there.

minority80

sorry Phil, i don't see any expressions box/row in the properties window... :

is this component depend on the sql server service pack installed in the machine

DarrenSQLIS

This is the built in Properties window for Visual Studio, not the component level UI form.

For Data Flow components, you don't see the Expressions on the component, you must first select the Data Flow Task, just click the icon if in Control Flow, or just click the background if you are in the Data Flow design tab. The Properties grid will no show properties for the task, and since is only tasks that can expose expressions, they should now be visible.

All component expressions are actually listed at the top of the properties grid, or can be accssed via the Expressions property as with normal tasks.

Phil Brammer

DarrenSQLIS wrote:

For Data Flow components, you don't see the Expressions on the component, you must first select the Data Flow Task, just click the icon if in Control Flow, or just click the background if you are in the Data Flow design tab. The Properties grid will no show properties for the task, and since is only tasks that can expose expressions, they should now be visible.

Darren is correct, which is why I shouldn't answer questions unless I'm in front of SSIS to validate my response. I should've known better that you access data flow object's expressions via the control flow.

minority80

Thanks Phil, Darren, John...

i just found out someone has posted the same question before.

http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=403771&SiteID=1

sorry for repeating. previously i've been searching for row count transformation and couldn't find the answer i'm looking for. thanks for answering...

i still couldn't get the answer.

i've assigned the max(id) value to a variable and place the variable+1 in the expression box.

works fine, no error but instead of returning the next number for new values, the id column for the dimension starts incrementing again..

e.g.

1st run:

Id BusinessKey BusinessName
--- ------------------ ---------------------
1 RDO Radio
2 MAG Magazine
3 TV Television

2nd run:

Id BusinessKey BusinessName
--- ------------------ ---------------------
4 RDO Radio
5 MAG Magazine
6 TV Television
7 NWS Newspapers

i've already set the Id as fixed attribute in SCD... :- :-(

jwelch

I don't think the ID column should be referenced in the SCD component as a fixed attribute. That's just the surrogate key you are assigning to the dimension, right

From your example, I'm guessing that the BusinessKey column is what defines a row as being a match for the existing row.

minority80

yes, the id column works as a surrogate key, shouldn't it be a fixed attribute

i defined the BusinessKey column as Business Key under Key Type in SCD while Id column is defined as Not a key column under Key Type.

Phil Brammer

I think you might want to add the row number transformation between the SCD and its Insert destination. You shouldn't have the primary key involved in the SCD -- only the BusinessKey. When a new record comes in (as a result of a historical attribute change or simply a new record) it will get assigned the next counter out of the row number transformation when it is placed between the SCD and the OLE DB insert destination.

minority80

thanks everyone...