SSIS 2014 - Flat File Destination with Different Number of Columns

1
votes

I've got an SSIS package (SQL 2014) that loads data from a table into a flat file. The file has 5 columns, however there is one row in my dataset that is used by the system for duplicate checking, and its required to have 3 columns, instead of 5.

How my file looks like now:
ID|Desc|UDF1|UDF2|UDF3
DUPECHECK|SaysSomethingIrellevant|||
ID1|Desc1|||
ID2|Desc2|||

How I want my file to look:
ID|Desc|UDF1|UDF2|UDF3
DUPECHECK|SaysSomethingIrellevant|
ID1|Desc1|||
ID2|Desc2|||

You can see how the second row of the file should have a different number of columns than the rest of the rows. How am I able to do this?

1
sql-serverssissql-server-2012ssis-2012msbi
I might be mistaken, but as far as I know, this is not possible at all! Should be the same logic as with tables: Each row of a table has the same number of columns. Anyways, if you need this specific structure, I would suggest creating a Script Component as destination and write the flatfile from there. – Tyron78
I was hoping there was a fancy workaround where I didn't need to use a Script task, but thanks for the input. – Pops

1 Answers

2
votes

You cannot do it. The only way I did it(I had to write a file with a header row and footer row which had different number of columns) is to either write everything (all the columns) to a row with a single column or to write three different txt files and then combine the the three file using a bat file.