April 24, 2008 at 4:08 am
How do I implement a SSIS package for the following requirement
the requirement is as follows
I have a scenario in ETL, Here are my source table and destination table
Source:
SINo,DeptNo,Name,AgentCodes
1,10,aaaa,1,2
2,10,bbbb,2,3
3,10,cccc,3,4
4,10,dddd,1,2,3
5,20,ffff,3
6,20,gggg,4
7,30,hhhh,1,2,4
8,30,iiii,2,4
9,30,jjjj,3,4
10,30,kkkk,1,2,4
11,30,llll,2,3,4
12,30,mmmm,1,3,4
13,30,nnnn,2,4
14,40,oooo,1,4
15,40,pppp,2,3
16,40,qqqq,3,4
17,50,rrrr,1,2
18,50,ssss,1,3
19,50,tttt,1,4
20,50,uuuu,1,2,3,4
Destination:
SINo,DeptNo,IncNo,Name,AgentCodes
1,10,1,aaaa,1,2
2,10,2,bbbb,2,3
3,10,3,cccc,3,4
4,10,4,dddd,1,2,3
5,20,1,ffff,3
6,20,2,gggg,4
7,30,1,hhhh,1,2,4
8,30,2,iiii,2,4
9,30,3,jjjj,3,4
10,30,4,kkkk,1,2,4
11,30,5,llll,2,3,4
12,30,,6mmmm,1,3,4
13,30,7,nnnn,2,4
14,40,1,oooo,1,4
15,40,2,pppp,2,3
16,40,3,qqqq,3,4
17,50,1,rrrr,1,2
18,50,2,ssss,1,3
19,50,3,tttt,1,4
20,50,4,uuuu,1,2,3,4
Like this, I have a source table with 4 columns(SINo,DeptNo,Name,AgentCodes) and destination table with 5 Columns(SINo,DeptNo,IncNo,Name,AgentCodes)
My requirement is, in source I have 4 rows with same deptno 10 and 2 rows with deptno 20 and so on.
But in destination the column for IncNo I need to store the 4 rows for deptno 10 is 1,2,3,4, and 2 rows for deptno 20 are 1,2 and 7 rows for deptno 30 are 1,2,3,4,5,6,7 and so on.
Can anybody help in this regard
Thanks in Advance
Sathish
April 25, 2008 at 2:56 pm
Sathish,
You can use the ranking function ROW_NUMBER() to generate the destination table you have described.
Ex. SELECT SINo, DeptNo, ROW_NUMBER() OVER (PARTITION BY DeptNo ORDER BY SINo) AS IncNo, Name, AgentCodes FROM Source
Your SSIS package would read the Source data and place it into a temporary table. An Execute SQL Task could then be used to INSERT INTO the Destination table from the temporary table using a SELECT statement similar to the one above.
Tom
April 28, 2008 at 12:24 am
Thanks Thomas,
it is working as a Query. But my source file is a Flat File, so when iam trying to use OLEDb command in Dataflow task, it is not working.
can you please suggest me in this regard
Thanks & Regards
Sathish
April 29, 2008 at 9:05 am
Sathish,
Your SSIS Control Flow tasks would be as follows:
1. Execute SQL - Create temporary table
2. Data Flow - Load data from source flat file into temporary table
3. Execute SQL - INSERT INTO destination table from the temporary table using a SELECT statement with a ranking function.
4. Execute SQL - Drop temporary table
Tom
April 30, 2008 at 4:43 am
Thanks Thomos for again given your valuable solution.
ok i will try as you told.
and i also resolved this problem by using Script Component.
in that i take an output colutm(DerIncNo) and wrote the following code
Public Class ScriptMain
Inherits UserComponent
Dim intCounter As Integer = 0
Dim PreDeptNo As Integer = 0
Dim CurrDeptNo As Integer
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
CurrDeptNo = CInt(Row.DeptNo)
If CurrDeptNo <> PreDeptNo And PreDeptNo <> 0 Then
intCounter = 1
Else
intCounter = intCounter + 1
End If
Row.DerIncNo = intCounter
PreDeptNo = CInt(Row.DeptNo)
End Sub
End Class
Thanks
sathish
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply