SSIS Row Number as Field - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

SSIS Row Number as Field

Author

Topic

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2010-02-26 : 02:14:27

is there anyway to populate a variable as the row number, and then use that in a column?

for example... i have a flat file, that does a multi-cast and i split the data into two tables.

table a
FirstName
LastName
Address
City
State
Zip
UID (this would be my row number)

table b
dob
gender
age
UID (same deal here, should match the first UID from tablea)

thanks!

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-02-26 : 02:20:55

Try this]

SELECT * , ROW_NUMBER() OVER(Partition by FirstName,LastName,Address,City,State,Zip Order by FirstName ) AS UID FROM table a

Vabhav T

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-26 : 08:10:47

quote:
Originally posted by albertkohl

is there anyway to populate a variable as the row number, and then use that in a column?

for example... i have a flat file, that does a multi-cast and i split the data into two tables.

table a
FirstName
LastName
Address
City
State
Zip
UID (this would be my row number)

table b
dob
gender
age
UID (same deal here, should match the first UID from tablea)

thanks!

you could just add a rownumber transformation and add it as a new column in metadata

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

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2010-02-26 : 12:37:07

they are all coming though as 0 for the row count... all i did was setup a variable, and then set the rowcount to that variable... when i map the variable to the field on the OLE destination, i get 0's all the way down :(

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-26 : 23:23:36

quote:
Originally posted by albertkohl

they are all coming though as 0 for the row count... all i did was setup a variable, and then set the rowcount to that variable... when i map the variable to the field on the OLE destination, i get 0's all the way down :(

not rowcount. that should be rownumber

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

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2010-02-27 : 01:55:10

not sure what you mean, this something i add into a derived column or something? remember, mad-nubbish over here :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-27 : 01:56:36

see

http://www.sqlis.com/post/Row-Number-Transformation.aspx

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

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2010-02-27 : 02:39:29

i found the row transformation. thanks that works perfectly!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-27 : 02:46:03

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources