I have a nightly SSIS package that utilizes a ForEach Loop enumerator to loop through similar MSSQL databases to query (3) different tables on a single SQL server.
(2) of the (3) tasks perform successfully every night. However, one of the tasks that queries vendor information will sometimes (without any rhyme or reason) write the data to the flat file destination without header information. The SSIS job step does not report any type of error when this happens so the files get sent to another system for ingestion and fails due to missing header information.
I have confirmed that the Column names in the first data row option is selected in the Flat File Connection Manager Editor on all (3) data flows.
My solution is to create a second process that will manually write the header info into the flat file destination and then concatenate the data rows without header info to that file.
Has anyone experienced this type of missing record info?
Is my potential solution the only way to address this issue?