Is there a way to export huge amount of data (more than a million rows) from SQL Server to csv?

Question

I have no trouble exporting to csv files using ssis when the number of rows is less than 500k. I get the following error when the size gets bigger

DT_TEXT, which is not supported with Unicode files. Use DT_NTEXT instead and convert the data to DT_TEXT using the data conversion component.

Answer 1

Use Management studio

• Right click on the database and select Tasks > Export data.

• Select Data source as SQL Server, select the server name, authentication and database and click Next.

• Select Destination as Flat file Destination and browse for a .csv file and click Next.

• Select row terminator as {CR}{LF} and column terminator as comma{,} and click Next.

• At the end it will show the details of the rows which got exported to .csv file.

Answer 2

Do as error message suggest - make sure that you have DT_NTEXT type for columns of Flat File Source that are mapped to nvarchar columns in the database. To do so click "Show advanced editor" on Flat File Source, than go to Input and Output Properties > Flat File Source Output > Output columns