Sometimes it’s the stupid things that trip you up. Especially when coding.
I was working on a new SSIS package that is supposed to append data to existing files created by a previous package. For reasons too long to go into, I couldn’t update the original SSIS package. I had to create a new one. So I did, ran it and the whole thing succeeded. The flat files, which started off with 4 rows each as a test, inflated in size, but when I opened them up, I could still only see 4 rows.
I was so frustrated. I googled the issue and searched everywhere. I checked my Flat File Destinations and the Flat File Connection Managers and couldn’t find a single problem. Then it occurred to me to re-check my files.
Sure enough, my new data was appending, but it appended on the very last row of the current file instead of creating new rows. And the connection managers didn’t have the Row Delimiter property in the regular edit windows.
Turns out Row Delimiter is hiding in the dockable properties window. Once I added my carriage return and line feed characters to this property, everything worked properly and the data appended in individual rows instead of to the fourth line of my text files.
So happy now, but kind of annoyed at Microsoft. Why would they hide the Row Delimiter instead of sticking it in the main edit window? And you can’t search BOL’s index page for Row Delimiter. It’s like you have to read their minds to know where it is.
SSIS is so counter-intuitive. @sigh. Oh, well, back to developing.
