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,