I will have a SQL Server 2012 database and a table with 3 million rows and maybe 50 columns. What will be the fastest way for an unattended background .net process (maybe issues some SQL or Powershell command) to export it to a text file, one line for each row of data? The .net process should know when the export have been finished or if there was any error. The datatype will be all
I am assuming that a pure C# code using ado.net to execute a
select * command and looping over the datareader and writing to a file for each record will be slow and there is no way I can parallelize this.
Ideally the export will be to a remote shared network folder and not a local folder on the SQL Server machine. The SQL Server will be a HA cluster. Is SSIS better suited for this, no data transformation required?
The .Net process would run on Machine A, SQL Server on Machine B and ultimate file destination is a network share. One option is SQL server writes the file directly to the network share. The other option is SQL Server writes to machine A and then when the file is written the .net process copies it to network share. I don't have formal SLA but expecting 30 mins - 1 hr for the file write.