April 6, 2011 at 11:33 am
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
April 6, 2011 at 1:30 pm
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
April 6, 2011 at 1:46 pm
Thanks, Mr or Mrs. 500. I check it out.
April 6, 2011 at 2:00 pm
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
April 6, 2011 at 2:08 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy