Thank you Allen. I am not very familiar with SSIS. The SQL code basically Pull revenue & GM by client for 5000 Client from SQL Server A To SQL...

Sorry a small addition: the first section of the code pull data from another DB over a linked server...The rest of section Is basically data validation, aggregation and calculation ...last...

To Clarify it is just One database we are talking about.

So the question is : would I create one SSIS with 4000 line of SQL code or I need...

I haven't used SSIS before, will it handle this giant 4000 Line Code?

That's a lot of work indeed..

Thanks,

Phil - Do you mean a Master SP that will call 25 smaller SP? assuming I am turning each section (Chunk) to a SP.

Thanks,

Do you have an example for upSert, Not sure if I understand your solution.

Thanks

Thanks everyone, I will create CI and see how it goes.

Appreciate everybody opinion.

Does anyone has an example to follow close to what I am trying to do (create a partition on trx_date)?

Thanks to all.

Lynn,

I have done table partitioning in the past. Can you point me to a sample to follow.?

Thanks a lot.

Yes every month.

Thanks

Good Point Jeff.

Reason is the process is happening so fast within couple of hours and with every iteration actual production reports are executed and checked until an 'ok' is...

I will try that Scott shortly.

However is there a faster way to put this monthly 201508 on a partition and swap it every time we need to refresh it.?

Thanks

Sam

150K

1) No Trigger on the table, no cascade delete.

2) The delete takes about 7 to 8 Minutes.

Thanks