Loading data from SSIS Package

  • 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

  • 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

  • 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

  • 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

  • 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