Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

General SSIS package to export any table into .csv Expand / Collapse
Author
Message
Posted Monday, March 18, 2013 12:43 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 14, 2014 10:47 AM
Points: 235, Visits: 469
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
Posted Monday, March 18, 2013 1:02 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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
Posted Monday, March 18, 2013 1:17 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 14, 2014 10:47 AM
Points: 235, Visits: 469
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
Posted Monday, March 18, 2013 2:43 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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
Posted Saturday, March 23, 2013 7:14 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:04 PM
Points: 1,114, Visits: 2,192
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 »

Add to briefcase

Permissions Expand / Collapse