question

Deepak Sharma avatar image
Deepak Sharma 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.
sql-server-2005import-datadata
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image
Pavel Pawlowski answered
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/
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered
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.
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
What if you use the BCP command in side the ForEach container?
1 Like 1 ·
Pavel Pawlowski avatar image Pavel Pawlowski commented ·
@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.
0 Likes 0 ·
Pavel Pawlowski avatar image Pavel Pawlowski commented ·
Agree.. with the BCP in foreach container. Then you do not need the flat file connections at all.
0 Likes 0 ·
Leo avatar image
Leo answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Deepak Sharma avatar image Deepak Sharma commented ·
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).
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.