Post reply

2 number subtraction in ssis

  • October 31, 2011 at 1:17 am

    #250912

    hi

    i m having table like this

    empno ename sal

    1 kkk 21000

    2 fff 25000

    3 ggg 30000

    now i want to substrate (empno2) sal - (empno1) sal

    how to do this in ssis package

  • October 31, 2011 at 3:17 am

    #1400966

    Is there any business rule that says which salaries should be subtracted by other salaries?

    Or is it just random?

    Since you are trying to compare rows with each other (which is not a set-based operation), you'll probably need a script component as a transformation for that.

    For more information:

    http://msdn.microsoft.com/en-us/library/ms136114.aspx

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • October 31, 2011 at 3:38 am

    #1400975

    Thomas.s (10/31/2011)

    hi

    i m having table like this

    empno ename sal

    1 kkk 21000

    2 fff 25000

    3 ggg 30000

    now i want to substrate (empno2) sal - (empno1) sal

    how to do this in ssis package

    Could you set this as the dataflow source?

    SELECT a.empno, a.ename, a.sal, ISNULL(a.sal - b.sal,0) AS [sal - sal]

    FROM myStangeTable a

    LEFT OUTER JOIN myStangeTable b ON a.empno - 1 = b.empno

    Bear in mind that I'm guessing as to your business logic of which "sal" to minus from which "sal", so you may need to adjust to suit your rules.

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • October 31, 2011 at 10:36 pm

    #1401413

    hi,

    thanks for ur help..but i dont want query in sql i want only ssis package mapping in sql server 2008

  • October 31, 2011 at 11:25 pm

    #1401421

    For this problem, best solution will be to modify sql query

    If you still want to go SSIS way, use a Lookup, modify the query in advanced query to get a value less than current value or for last empId (as per your business logic), and finally use a derived column to subtract the salary value returned by Lookup from salary.

  • October 31, 2011 at 11:29 pm

    #1401423

    ya thanks..just i need empno1.sal -empno2.sal in ssis map.

    i dont want less than current value etc

  • November 1, 2011 at 12:12 am

    #1401430

    Use lookup...Join on EmployeeId...modify advanced query to something like

    select top 1 * from

    (select * from [dbo].[Employees]) [refTable]

    where [refTable].[Id]< ? Order by Id desc

    (there are other ways also to get last max value..this came ad hoc to me)

    Use derived column, subtract Salary returned by lookup from source salary.

  • November 1, 2011 at 2:39 am

    #1401450

    Thomas.s (10/31/2011)

    hi,

    thanks for ur help..but i dont want query in sql i want only ssis package mapping in sql server 2008

    That's why I said set it as the dataflow source.

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • November 2, 2011 at 12:56 am

    #1401984

    Thomas.s (10/31/2011)

    ya thanks..just i need empno1.sal -empno2.sal in ssis map.

    i dont want less than current value etc

    What exactly do you mean with "ssis map"?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • November 2, 2011 at 3:51 am

    #1402032

    If you don't want to make a change to the sql source,

    another way of doing this would be to add your data source twice, then use a derived column to minus one from the key of one data source.

    Then do a merge based on this key and do the subtraction in subsequent derived column.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply