asked
Convert the data of SQL Server DataBase in to CSV
Hi, I have a small SQL Server 2005 DB and it has 50 tables ( All tables have different Structure). Now I want to import the data of all tables into CSV. I can make a SSIS package for it but for this I have to make 50 FlatFile Connection Managers for mapping the data of Each table into CSV. Is there any way to do this into a single step.
You can use [BCP][1] and iterate through all tables. eg. using [sp_MSforeachtable][2] or any other methods eg. WHILE loop or cursor and selecting data from sys.tables There is also article for [sp_MSforeachtable on SQL Server Central][3] [1]:
http://msdn.microsoft.com/en-us/library/ms162802.aspx [2]:
http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm [3]:
http://www.sqlservercentral.com/articles/Advanced+Querying/sp_msforeachtable/181/
I think Pavel is right. BCP with either sp_msforeachtable or else handwritten dynamic SQL is probably the best way to go. You could also have SSIS dynamically create the connections it needs instead of creating 50 connections manually.
What if you use the BCP command in side the ForEach container?
@Timothy.. Using the SSIS for this purpose I see the only problem with the Column Mappings for the FlatFile. You can simply modify the destination file for the flat file the connection Manager in the ForEach container, but the columns will be a problem as they will be different for each table. If you should export 50 tables with the same structure, then there's no problem.
Agree.. with the BCP in foreach container. Then you do not need the flat file connections at all.
Hi Deepak, You can use SQL Import and Export Wizard in SQL 2005. It will let you more control for exporting the Tables, Views , etc... You don't need to write SSIS package for them.
Thank you Leo, but when I will use Import Export Wizard, It will not let me choose more than one table to Export in CSV, I can use more than one table when I will use Import Export Wizard from one OLEDB source to another or same OLEDB Destination but it is not possible in case of OLEDB source and FlatFileDestination (for CSV).
