Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Create Multiple Excel output files Expand / Collapse
Author
Message
Posted Wednesday, October 17, 2012 5:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 13, 2014 11:15 AM
Points: 145, Visits: 373
Hi everyone - hope someone can help...

I have a SQL table that contains information on visits to our centre. The table contains a number of columns but the ones I'm interested in are: CentreName, DateOfVist and VisitorNumbers. I would like to create an SSIS job that would pull in the visitor information for the current month from this SQL Table then split the data by CentreName (either North, East, South or Central) before finally exporting the split files to Excel to a named folder (ideally naming the files with the name of the Centre and the reporting month and year).

Any suggestions?

Thanks in advance.

BO
Post #1373733
Posted Wednesday, October 17, 2012 6:44 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, December 19, 2014 7:50 AM
Points: 1,234, Visits: 1,273
ByronOne (10/17/2012)
I have a SQL table that contains information on visits to our centre. The table contains a number of columns but the ones I'm interested in are: CentreName, DateOfVist and VisitorNumbers. I would like to create an SSIS job that would pull in the visitor information for the current month from this SQL Table then split the data by CentreName (either North, East, South or Central) before finally exporting the split files to Excel to a named folder (ideally naming the files with the name of the Centre and the reporting month and year).

In your Data Flow task you could:

  • Use an OLE Source to query your database for the visits you're after

  • Use a conditional split to break out for North, East, South and Central

  • Direct the four outputs from the conditional split to a seperate Excel destination component


You can use a variable to store the date and put that in the expression for your Excel destination so your file names are something like: South20121017.xls.

I hope that helps at a high level,
Rob
Post #1373764
Posted Wednesday, October 17, 2012 5:03 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 13, 2014 11:15 AM
Points: 145, Visits: 373
Cheers Rob

That's the pointer I needed!

BO
Post #1374082
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse