Import/export data from SQL Server programmatically using C# and ASP.NET

Question

I have a web app written with C# to run in an ASP.NET environment. The web app is using SQL Server 2008 database (T-SQL) to store data. It has about 4-5 tables with data. So I was wondering if there's anything in ASP.NET to help me export those tables along with all of their data into a text file (or a memory array) programmatically, as well as subsequently import them back into the SQL Database later (obviously from a file or memory array)?

PS. The reason I need this is to provide a function in the web app itself to import/export data from another copy of the web app that runs on a separate IIS.

Answer 1

You can build an SSIS package to do whatever copying you need to happen. Parameterize the package so you can vary the source and destination databases / connection strings, etc.. Execute the package from your application.

You can even build the SSIS package programmatically if you need to. The Microsoft reference appears to be down at the moment, but this SO post has the basic links you would need to get started on the programmatic front.

Just a warning that although programmatic SSIS package building works great, it can be pretty complicated, and the documentation for many aspects of the SSIS internals/API is sparse if it exists at all. It's best to start with building a package through the SSIS Management Studio application first.