locked
Eliminate StoredProc RRS feed

  • Question

  • HI, How can i eliminate this stored procedure and insert into that table using SSIS. Please help

    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    ALTER PROCEDURE [dbo].[SP_CALC_GPER] AS
    Create table #tempsum  
    (budget_code varchar(10) Not Null, 
      sumamt numeric(12,2) Null
    )
    Insert into #tempsum
      Select budget_code,sum(amount) from gsaltdr
      group by budget_code
    Delete from #tempsum where sumamt=0.00
    Delete from #tempsum where sumamt is null

    truncate table gtdrper

    Insert into gtdrper
     SELECT gsaltdr.budget_code,   
             gsaltdr.business_unit,   
             gsaltdr.product,   
             gsaltdr.product_src,   
             gsaltdr.amount/#tempsum.sumamt 
        FROM gsaltdr, #tempsum
    where gsaltdr.budget_code = #tempsum.budget_code


    Tuesday, May 15, 2012 2:59 PM

Answers

  • You can just paste the entire contents of the proc into a Execute SQL block in your control flow.

    Or use a dataflow with

    SELECT gsaltdr.budget_code,   
              gsaltdr.business_unit,   
              gsaltdr.product,   
              gsaltdr.product_src,   
              gsaltdr.amount/tempsum.sumamt 
         FROM gsaltdr
    	 JOIN (	Select budget_code,sum(amount)sumamt  
    			from gsaltdr
    			group by budget_code
    			HAVING colaesce(sum(amount),0.00) != 0.00) tempsum
    	ON  gsaltdr.budget_code = tempsum.budget_code


    As your source


    Chuck






    • Edited by Chuck Pedretti Tuesday, May 15, 2012 3:07 PM
    • Proposed as answer by Eileen Zhao Friday, May 18, 2012 8:54 AM
    • Marked as answer by Eileen Zhao Monday, May 21, 2012 3:15 AM
    Tuesday, May 15, 2012 3:03 PM