February 28, 2014 at 5:39 am
Hi everyone - hope someone can help.
I want to create a script task in SSIS that will export 3 tables from SQL into 3 separate csv files in the file directory ie Table1 = csv1, Table2 = csv2 and Table3 = csv3.
I have tried - and failed - using the flat file destination task so thought I'd try another approach - I was able to loop through each of my tables but rather than generating 3 csv files it only ever created one csv with the contects of the final table in it.
Any help greatly appreciated.
BO
February 28, 2014 at 6:03 am
Is there a reason for using a script task versus a data flow?
February 28, 2014 at 6:18 am
Only that that the flat file option is proving problematic...
I was thinking perhaps the script would be more dynamic...
February 28, 2014 at 6:20 am
Depending on the volume of data we generally go for BCP when exporting data from a table to CSV. This give great flexibility generating the file name on the fly.
February 28, 2014 at 6:23 am
Doesn't bcp lose the headings? Or do you have example script that retains these during Export?
February 28, 2014 at 6:28 am
It does yes but we get round this by writing a custom query to create the headers and add that as part of the BCP script
February 28, 2014 at 6:53 am
michael.higgins (2/28/2014)
It does yes but we get round this by writing a custom query to create the headers and add that as part of the BCP script
Yes, this is the approach I take when we have complex outputs. Build your "file" into a table then export the table using SSIS, or BCP if that is what you like.
We have a file the needs to be header then detail, then the next record and it's detail, etc. We build the table with 2 additional columns, record id and level. the select for the flow orders by recordid,level, but does not write the columns to the file. Processing time dropped from hours to minutes since the data preparation is set based, not row by row.
February 28, 2014 at 7:00 am
Thanks to you both I'll give it a go...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply