Solved

How to create Counter variable in Foreach loop container SSIS 2008

Posted on 2011-02-28
4
1,369 Views
Last Modified: 2013-11-10
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
Comment
Question by:vepak
  • 2
  • 2
4 Comments
 
LVL 26

Expert Comment

by:tigin44
ID: 35002061
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
 

Author Comment

by:vepak
ID: 35002314
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
 
LVL 26

Accepted Solution

by:
tigin44 earned 500 total points
ID: 35002379
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
 

Author Comment

by:vepak
ID: 35002434
Thanks man it worked. Awesome.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Add a step to a system backup job 6 20
MS SQL: Getting all rows not just one , combining multiple queries 11 27
sql, case when & top 1 14 30
SQL Recursion schedule 13 19
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question