SQLTeam.com | Weblogs | Forums

Data extract to xls file or ssis?

tsql
sql2012
sql2008

#1

I am hoping someone can advise the best way to store data outside of the sql server for future use.
I would like the ability to query this data at a later time when there is no associated sql server
some reporting tools, like crystal, allows you to report from flat files.
i need to extract full tables of data.
I queried on table for just 1 months data and the row exceeded the excel storage ability.
so keeping them as flat files is not an option.

i would like to know if anyone has encountered this issue and if they have a solution.
i am looking into SSIS but i am not familiar with that tool to make a decision if it is the way to go.
does anyone have any suggestions on the best way to handle this?


#2

Probably answering the wrong question, but we have tables with very high turnover rates - many millions of rows added each day, and the data is purged after 7 days.

We export the data using BCP in Native format, and then delete it from the database.

If we need the data again we import it back into the database (well ... usually a copy database). We almost never need the data again (pretty much the only time has been a fraud investigation, or similar, and thankfully they are very rare)

If I thought I would need the data again (as compared to thinking that "most likely" I would never need it again)I would be wanting to keep it in a database, rather than "something else"