General SSIS package to export any table into .csv

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » General SSIS package to export any table into...

General SSIS package to export any table into .csv

Rate Topic

Display Mode

Topic Options

Author

Message

sql_er

Posted Monday, March 18, 2013 12:43 PM

SSC Veteran

Group: General Forum Members
Last Login: Friday, May 03, 2013 10:08 AM
Points: 223, Visits: 424

Hi guys,

I need to create an SSIS package that takes a table name as a parameter and exports out its data in a CSV file.

The challenge is that if I use a data flow task and a flat file connection manager (for csv export), I have to specify the file/table structure. I will not know the table structure until the run time because it is input specific.

Has anyone had a similar problem and implemented a solution?

I was thinking along the lines of using C# in a script task to get the column structure of the input table, read the data and write it out into a CSV file. I could not come up with a full blown solution though.

Any advise is appreciated.

Thanks!

Post #1432309

opc.three

Posted Monday, March 18, 2013 1:02 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:57 PM
Points: 6,724, Visits: 11,771

Calling bcp might be a better option for you than using a Data Flow since bcp does not need to be armed with the table structure if using the bcp out syntax and supplying a table name, e.g.

bcp DatabaseName.SchemaName.TableName out ...

You can call bcp from SSIS using an Execute Process Task and use an SSIS Expression to build the command line on the fly, or you could simply call bcp directly and skip SSIS altogether if all you need to do is dump the table to a delimited file.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1432315

sql_er

Posted Monday, March 18, 2013 1:17 PM

SSC Veteran

Group: General Forum Members
Last Login: Friday, May 03, 2013 10:08 AM
Points: 223, Visits: 424

Hi opc.three,

I thought about bcp, but my sense (from what I heard as I have not worked with it much) is that there could be access issues with running bcp.

What are your thoughts about it?

Thank you for suggestions!

Post #1432320

opc.three

Posted Monday, March 18, 2013 2:43 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:57 PM
Points: 6,724, Visits: 11,771

Not that I am aware of, in general. Maybe something in your particular environment? Define "access"? Windows? Database?

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1432345

CozyRoc

Posted Saturday, March 23, 2013 7:14 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 10:02 AM
Points: 1,106, Visits: 2,115

For the process you want to accomplish using the standard SSIS will not be much of help and will in fact make your solution even more complicated.

If you still want to use SSIS, I would strongly recommend you check the commercial COZYROC Data Flow Task Plus. It is an extension of the standard Data Flow Task , with ability to do dynamic data flows at runtime. If you decide to use it, you can accomplish the requirement with no programming skills required, exporting arbitrary table into a flat file.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Post #1434640

« Prev Topic | Next Topic »

Permissions