August 25, 2009 at 12:51 am
Hi ,
I have a stored procedure which excepts a xml data.Based on the xml data it returns a report. I need to write this report to an excel file.
I know that we can do this via script task and also via Excel destination.
My problem is this report will contain data about six months old...ie a very large amount of data.
ie loading the data in one time fetch will cause the sp to hang up.
more over the sp work flow have a lots of complex queries so to make a ssis proto of that sp is very difficult.
So what i planned is to retrieve a batch records say 1000 from the sp and write to the excel. Then take the next batch records and append it to the excel.....is it possible to do so?
Please suggest a way to implement this...
Thanks,
Naveen
August 25, 2009 at 2:45 am
Do you know in advance how many records will be written? If so, maybe a For loop could do it.
But 1,000 lines is nothing. How many rows (approximately) are you talking about? Excel has limits (which vary depending on the version of Excel you are using). If there are tens of thousands of rows, I would suggest that Excel is not the right place to be sending them & maybe a CSV file would be better - depending on your requirements, of course.
--Edit
Or why not send all of the output from the sp to a new physical table in your SQL Server database and then just export that straight to Excel in one chunk at the end?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply