getting reports in an excel sheet on a data pattern

Question

getting reports in an excel sheet on a data pattern

somareddy.svr

SSC Enthusiast

Points: 140
More actions
October 27, 2008 at 4:37 am

#124432

Hi all,
I have a problem in getting reports in an excel sheet
for each day from a table that contains
SALES for EACH DAY from 2008-01-01 - till today.
Every day there are approximately 8500 records.
So my Full_Sales table contains 1,450,356 rows..(approx)
Now, I have added a date column in the table to see which day what was sold...
if you filter on day it will give you 8500 rows sold that day...
there are total 162 days from so I want to give them 162 excel sheets from 1st jan --- till 10th june...
I took one OLE db src (main SALES table)
the conditional split with 162 date conditions
then 162 Destination Excel sheets
query is like this:
--------select * from dbo.Historical_sales
--------where sales_Date = '2008-06-08'
----------this result - 8545 rows (sales of one day)
So, Is this the right way...of getting soln. It seems tough to add these many destn. I know I have to use For each loop editor. But I'm pretty much lost here..
Any ideas, please respond....asap
Thanks in advance
Regards,
Soma

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

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

hodgy SSCertifiable Points: 5906 More actions · Answer 1

you could use a for loop to do all the hard work for you on this one.

you set the for loop to use a date as the limit, so you can set the start and end dates. within the loop, you have a script task that will create a new sheet in the spreadsheet. you would also have your data flow in the for loop, and build the source sql using the date (you can in theory use parameterised queries as your source but i usually find it a lot easier and more straightforward to use a dynamic variable to hold the sql). also, use the same principle to build the destination sql for the excel sheet.

as the only thing changing is the date, it makes sense to use a loop and change that one thing.

tom

Life: it twists and turns like a twisty turny thing

somareddy.svr SSC Enthusiast Points: 140 More actions · Answer 2

Hi, thanks for the response

I'm new to the SSIS so,this solution seems pretty much brief and straight forward to me. I'm unaware of how to use script task. I understand taking for loop and using date limit, giving starting and ending dates. But we nee a condition(To incraese the date), how can we give that? After that, how can we use a script task for doing this?

I also tried using varible to hold sql, (select * from dbo.Historical_sales where sales_Date = "@vDATE") but I got a problem at capturing the datepart with a variable vDATE?

I know this costs a lot of patience. But, please try adding more details to the solution.

Thanks in advance

Soma

hodgy SSCertifiable Points: 5906 More actions · Answer 3

the For loop will manage the conditions needed to control the loop. In the For Loop section of the editor you have three properties: InitExpression, EvalExpression, AssignExpression.

InitExpression: this will initialise your variable, e.g. @vDate="2008-01-01"

EvalExpression: this is the check that keeps the loop running. Needs to evaluate to true for the loop to run, e.g. @vDate<GetDate()

AssignExpression: this changes the variable being evaluated so that the loop will eventually finish, e.g. @vDate=DateAdd("day", 1, @vDate)

This example above will execute the loop for every day between the beginning of the year and now.

The script task would use the @vDate variable to create a new sheet in your workbook. See here for an example

To use a variable to hold the SQL you will need to set the EvaluateAsExpression to true, and in the expression builder put something like: "select * from dbo.Historical_sales where sales_Date = '" + @vDATE + "'"

Hope this points you in the right direction.

Tom

Life: it twists and turns like a twisty turny thing

somareddy.svr SSC Enthusiast Points: 140 More actions · Answer 4

Hi,

Thanks a lot, I learnt a new concept here which I wasn't aware of

It's really helpful

Soma