Export to Multiple Worksheets

Question

Export to Multiple Worksheets

2Tall

SSChampion

Points: 14644
More actions
December 22, 2009 at 8:49 am

#137751

I posted a similar request last week however have now started a new post as progress has been made.
Using SSIS I have created a package as follows:
File System Task (DestinationConnection export.xls / OverwriteDestination = True / SourceConnection = template.xls)
Data Flow Task
OLE DB Source
SQL Command
EXEC bsp_GLExport @GLType = 2666, @Year = 2010
Excel Destination
Sheet 35070$
This runs OK. Data is written to the exports.xls file, worksheet 35070$. Each time the pacakge executes the file is overridden.
I would like to execute the package multiple times (multiple tasks) writing values to additional worksheets within the exports.xls file
i.e. second run
EXEC bsp_GLExport @GLType = 2670, @Year = 2009
Excel Destination
Sheet 35080$
Can I achieve this in my current package?
Many Thanks,
Phil.
Update: Would I add additional Control Flows?
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper

Viewing 4 posts - 1 through 3 (of 3 total)

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

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 1

That would be the easiest way of accomplishing this task. Simple add more controls that do basically the same thing as the original ones. It would not hurt to hit the database more than once. You could also create a complete new package, and call one from the other. That way you could easily stop using one if needed

Andrew SQLDBA

2Tall SSChampion Points: 14644 More actions · Answer 2

Thanks for posting.

Kind Regards,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Phil Parkin SSC Guru Points: 246974 More actions · Answer 3

Well, within reason, but if you're going round pretty much the same loop 20 times, I would be doing a fair amount of investigation to avoid having 20 dataflows.

A foreach loop could do this, it would seem, by (for example) reading its parameters from a pre-populated SQL Server table, though multiple DFs is an easier-to-code solution, I agree.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel