January 31, 2013 at 5:54 am
Hi All,
This is Vivekanand,i have one requirement in ssis that is load data into excel file from table.Table contain 10000 records but each sheet in excel file allow 1000 records only using single DFT.Can any one give information how to resolve the requirement.
Thanks,
Vivekanand,
January 31, 2013 at 6:14 am
The first thing you need to know about SSIS is that it is not flexible or dynamic.
If you say you have 10,000 rows and you need 10 Excel sheets, then it cannot change later on. You create 10 destinations and you stick to those 10 destinations.
If you are sure of your 10 destinations, you can simply read the 10,000 rows, add a rowcount and use the conditional split to route the rows to the 10 different destinations (you can use the modulo function).
Another option, if you need to be a bit more dynamic, is to loop over the table, read 1000 rows in each iteration and write it each time to a different sheet (use an expression on the destination).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 31, 2013 at 9:31 am
Hear you have create excel sheets dynamically while loading data into excel.can you provide steps of easy to reach:-)
January 31, 2013 at 6:27 pm
viveksvkola (1/31/2013)
Hear you have create excel sheets dynamically while loading data into excel.can you provide steps of easy to reach:-)
I'd be interested in hearing how to do that too!
And most especially if there's a way to create a new worksheet based on an existing one.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 31, 2013 at 11:48 pm
You use a CREATE TABLE statement in an Execute SQL Task:
Creating Excel Worksheets Dynamically in SSIS
@dwain: you can use a script task to get the metadata from an existing sheet. Then build your CREATE TABLE statement dynamically, put it in a string variable and use that variable in the Execute SQL Task.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 3, 2013 at 5:29 pm
Koen Verbeeck (1/31/2013)
You use a CREATE TABLE statement in an Execute SQL Task:Creating Excel Worksheets Dynamically in SSIS
@dwain: you can use a script task to get the metadata from an existing sheet. Then build your CREATE TABLE statement dynamically, put it in a string variable and use that variable in the Execute SQL Task.
Actually I was wondering if it can be done through an OPENROWSET query. I should have been more clear.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply