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

Question

Post reply

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

DoCHoliday

SSC Journeyman

Points: 91
More actions
April 6, 2011 at 11:33 am

#248492

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