Dump all tables to files using SSIS

Question

Dump all tables to files using SSIS

Paul_J

Hall of Fame

Points: 3698
More actions
February 18, 2008 at 7:03 pm

#120411

I want to create an SSIS package to loop through all tables in a database and dump them out to csv files.
Does anyone see any problems in achieving this?
I can see a potential problem when setting up the data flow columns metadata since each table will have a different number of columns...
All input to this problem is appreciated.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQLdevotee SSCarpal Tunnel Points: 4820 More actions · Answer 1

This should not be a problem. I have done this before. Please correct me if I am wrong. You want to dump ALL the tables in SQL database to ONE flatfile destination? If I am right, then all you have to is union all every table you have. You don't have to worry about the columns as they get to the destination without a problem.

[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]

Paul_J Hall of Fame Points: 3698 More actions · Answer 2

Actually what I want to do is export all tables to separate files. E.g. Table1 goes to Table1.csv, Table2 goes to Table2.csv, etc.

I'm actually going to take it a little further so that each table is classified into a separate directory, based on the schema it is in... but I don't think that affects this problem.

I have it all working through code (Script task and VB.NET) but wanted to do it using the OLE DB Source and Flat File Destination things since they do most of what I want. (SIDE NOTE: I have noticed though that the Flat File Connection Manager does not parse values with quotes correctly - ie replace " with "" when they are present in a cell value)

VALEK SSCarpal Tunnel Points: 4206 More actions · Answer 3

Paul (2/19/2008)
Actually what I want to do is export all tables to separate files. E.g. Table1 goes to Table1.csv, Table2 goes to Table2.csv, etc.
I'm actually going to take it a little further so that each table is classified into a separate directory, based on the schema it is in... but I don't think that affects this problem.
I have it all working through code (Script task and VB.NET) but wanted to do it using the OLE DB Source and Flat File Destination things since they do most of what I want. (SIDE NOTE: I have noticed though that the Flat File Connection Manager does not parse values with quotes correctly - ie replace " with "" when they are present in a cell value)

Valid request. Everyone seems to be wanting to do exaclty this.

Look here (Dont forget to scroll all the way down for the answers)

The expert there says that it is possible ONLY if the structure of the output files and input tables are the same.

SSIS does not allow such flexibility that you just dump any table in a file, since column definition should be setup.

I would recommend you to stick to the solution you already have (using VB for looping and writing) unless it is really necessary to use source-destination pairs.

If so, and the structuress differ, it looks like you have to have hundreds of dataflow elements.

We have hundreds of DTS packages doing almost that and now with SSIS it seems like we will have almost the same.

Anyone has any ideas on this?

M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM

Frank Bazan SSCertifiable Points: 5378 More actions · Answer 4

There is a way that it can be done, but its messy... you use the bulk insert task, or if you want to output the data, use bcp and an execute process task.

Details of the method here[/url]

Kindest Regards,

Frank Bazan

Paul_J Hall of Fame Points: 3698 More actions · Answer 5

Thanks for the help everyone.

Yes, the column definitions seems to be a big problem.

I'll stick with the VB.NET code as it seems the most simple to understand (good for future maintenance).

We did consider BCP but decided against it for several reasons, one of which was the fact that it actually can't output the column names.

Cheers

Paul

schumi-440251 Mr or Mrs. 500 Points: 515 More actions · Answer 6

you can create a template of the .csv file somewhere and than everytime: you copy that template to wherever you want, rename it dynamically and write into it. I do this with an excel file.