How to loop through SQL Table from SSIS to get a set of record and export as excel

  • Hi All,

    I am newbie to SSIS package and in need of your help. What I am trying to do is I have a table that I must loop through export 49 rows at a time into an Excel until the last row is reach, so if there are total of 200 rows in the table then I should have five Excel files output. After days of searching the web, I kow that I need to use the Foreach Loop but have no clue on how to ?

    Can some one please help

    Thanks

    DocHoliday

    My SQL Script for selecting the data:

    SELECT @TotalRow = COUNT(*) FROM dbo.JournalEntry1231

    SELECT @StartRow = 1;

    SELECT @EndRow = 47;

    SELECT @ReStart = @StartRow + @EndRow

    SELECT @ReEnd = @ReStart + @EndRow

    WITH JournalDebit AS

    (

    SELECT je.SCE, je.db_cost_center, je.DB_sub_acct, je.CR_cost_center, je.CR_sub_acct, Entry_Amt,

    ROW_NUMBER() OVER (ORDER BY je.rec_id) AS RowNumber

    FROM dbo.Journal_entries je

    WHERE SCE = 'BL'

    )

    SELECT db_cost_center AS Cost_center, DB_sub_acct AS Sub_acct, CAST(SUM(Entry_Amt) AS MONEY) AS Debit, '' AS Credit

    FROM

    (

    SELECTSCE, db_cost_center, DB_sub_acct, CR_cost_center, CR_sub_acct, Entry_Amt

    FROM

    (

    SELECTSCE, db_cost_center, DB_sub_acct, CR_cost_center, CR_sub_acct, Entry_Amt

    FROMJournalDebit

    WHERERowNumber >= @StartRow

    AND RowNumber <= @EndRow

    ) AS Sub47

    )AS Grouping47

    GROUP BY db_cost_center, DB_sub_acct

    UNION ALL

    SELECT CR_cost_center AS Cost_center, CR_sub_acct AS Sub_acct, '' AS Debit, CAST(SUM(Entry_Amt) AS MONEY) AS Credit

    FROM

    (

    SELECTdb_cost_center, DB_sub_acct, CR_cost_center, CR_sub_acct, Entry_Amt

    FROM

    (

    SELECTSCE, db_cost_center, DB_sub_acct, CR_cost_center, CR_sub_acct, Entry_Amt

    FROMJournalDebit

    WHERERowNumber >= @StartRow

    AND RowNumber <= @EndRow

    ) AS Sub47

    )AS Grouping47

    GROUP BY CR_cost_center, CR_sub_acct

  • Too bad this in not being done on Denali (or is it? :-D) where we have the OFFSET/FETCH expression.

    Here is a video that may help you understand the basic use of the ForEach Loop:

    http://www.sqlshare.com/looping-through-and-loading-files-with-ssis_34.aspx

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Thanks, Mr or Mrs. 500. I check it out.

  • It's Mr 🙂 And my name is opc.three ... Mr or Mrs 500 is just my rating at the moment based on the number of points I have on the site. Good luck!

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Sorry about that.

Viewing 5 posts - 1 through 5 (of 5 total)

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