Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1389
  • Last Modified:

How to create Counter variable in Foreach loop container SSIS 2008

Hi There,

I was working on creating counter variable inside foreach loop container because, I'm having data like this

(stocknumber_randomID)
00005543_10                      
00005543_12                      
00005543_4564                  
00006644_567                    
00006644_768  

and desired output

(stocknumber_randomID)   NEW SEQUENCE
00005543_10                      1
00005543_12                      2
00005543_4564                  3
00006644_567                    1
00006644_768                    2
and so on.....

If I can use counter in foreach loop I can achieve this. Could you please suggest me how to setup incremental counter inside foreach loop

Thanks
Vamsi,
0
vepak
Asked:
vepak
  • 2
  • 2
1 Solution
 
tigin44Commented:
you may use the ROW_NUMBER function ie...

SELECT stocknumber_randomID, ROW_NUMBER() OVER (PARTITION BY LEFT(stocknumber_randomID,CHARINDEX('_',stocknumber_randomID-1)) ORDER BY stocknumber_randomID) AS newSequence
FROM yourTable
0
 
vepakAuthor Commented:
it says
Conversion failed when converting the varchar value '00003311_6718.jpg' to data type int.

as I have file names as 00003311_6718.jpg and so on....
0
 
tigin44Commented:
a misplaced paranthesisi  correct one is this

SELECT stocknumber_randomID, ROW_NUMBER() OVER (PARTITION BY LEFT(stocknumber_randomID,CHARINDEX('_',stocknumber_randomID)-1) ORDER BY stocknumber_randomID) AS newSequence
FROM yourTable
0
 
vepakAuthor Commented:
Thanks man it worked. Awesome.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now