SSIS Output to csv with differing columns in each row

Question

Post reply

SSIS Output to csv with differing columns in each row

Animal Magic

SSChampion

Points: 13964
More actions
June 27, 2013 at 10:20 am

#295740

Hi,
I have a requirement to produce a csv file with three differing number of columns.
For example i need to produce a file similar
H,ThisIsAHeaderRow
D,Details,Mr,Test,Person
F,Footer,2
Is this actually possible?
One horrible way i can think of doing it it output strings from sql so there is only 1 column and do it that way but there must be a nicer way than this
select 'H,ThisIsAHeaderRow'
Thanks in advance
John

Viewing 9 posts - 1 through 8 (of 8 total)

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

Phil Parkin SSC Guru Points: 246979 More actions · Answer 1

Are the header and footer static or dynamic?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Animal Magic SSChampion Points: 13964 More actions · Answer 2

Header is fixed. Footer is fixed in terms of column numbers but data is dynamic.

Phil Parkin SSC Guru Points: 246979 More actions · Answer 3

I would probably use a Script Component to do this - with a single output column.

The Script Component then needs to take care of adding all the delimiters and performing the concatenation, along with the special cases of first and final row.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Animal Magic SSChampion Points: 13964 More actions · Answer 4

OK cheers. One theory I do have is setting 3 data flow tasks all output to different files which have the same output path, hence windows seeing them as the same file. Set overwrite file to off and run them sequentially header,details then footer. That way each data flow task can have a defined output of the number of columns for its type and the mappings should work without hitch. Let's see if it works...

Phil Parkin SSC Guru Points: 246979 More actions · Answer 5

Animal Magic (6/28/2013)
OK cheers. One theory I do have is setting 3 data flow tasks all output to different files which have the same output path, hence windows seeing them as the same file. Set overwrite file to off and run them sequentially header,details then footer. That way each data flow task can have a defined output of the number of columns for its type and the mappings should work without hitch. Let's see if it works...

Good luck with that! Please let us know whether it works.

A variation on this theme is to write to three separate files and then have a final Execute Process command which does a copy/append of the files to concatenate them - I've successfully used this technique previously.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Animal Magic SSChampion Points: 13964 More actions · Answer 6

That also sounds a good plan! I'll see how it goes today and feed back. Just about too head into work now

Animal Magic SSChampion Points: 13964 More actions · Answer 7

The multiple data flow tasks has worked 😀

For anyone that happens upon this thread with the same issue, setup one data flow with a flat file output for your header, so if you have 3 columns in the header map that to three columns in your output.

Then do the details row, adding a new file allows you to do a completely new mapping, so you can add for example 30 columns.

Then do another data flow with your footer row.

Add a variable "OutputFile" and set that as the connection string for all three flat file output files. Turn off the "output headers/first row has header names" and also make sure file overwrite is turned off so that each output appends to the file.

John

Phil Parkin SSC Guru Points: 246979 More actions · Answer 8

Nice work, well done.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel