3

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.

ahmd0
  • 16,633
  • 33
  • 137
  • 233

3 Answers3

3

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.

Community
  • 1
  • 1
ulty4life
  • 2,972
  • 1
  • 25
  • 31
  • Thanks. Sounds like some complicated stuff, especially that this is the first time I hear about that SSIS... – ahmd0 Mar 13 '12 at 01:57
2

There is nothing in T-SQL specifically that allows you to interact with the file system, but there are various approaches for both directions. A few quick examples:

This question may also have some interesting commentary.

Community
  • 1
  • 1
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Thanks for the links. So, yeah, BULK INSERT seems like an answer to import data, although clearly it won't export foreign keys, relations, indexes, etc. Also I'm assuming that there's no BULK EXPORT, is there? – ahmd0 Mar 13 '12 at 01:17
  • No there is no BULK EXPORT, and these methods are just meant for flat/bulk data. If you're trying to back up indexes, keys, relations etc. you maybe want to think about a proper backup instead of exporting to flat files. – Aaron Bertrand Mar 13 '12 at 01:19
  • No, again, it's more like an easy way to move data from one copy of the web app to another... – ahmd0 Mar 13 '12 at 01:23
0

If I wanted to regularly copy data between two SQL Server instances as you mention I would use Linked Servers and then write a script that would make the move directly between the two servers.

Linked Servers (MSDN)

Craig Moore
  • 1,093
  • 1
  • 6
  • 15