I have an SSIS package that won't error out even with bad input. In my case this package reads from a flat file and puts records into a SQL Server table. Pretty straightforward, nothing fancy going on here.
The flat file is defined as being ragged right, 80 characters per row, maybe 10 columns in total. Problem: sometimes the flat file isn't padded out to 80 characters, so we get variable length rows, instead of spaces filling out the rest. We want to fail the package when that happens.
In my flat file source component I have the error output section set up so that if any column is truncated the component will fail. Yet for some reason all steps are green when I run the package from Visual Studio, even though no rows are imported. Here's the output I get:
SSIS package "SSIS Package 01.dtsx" starting.
Information: 0x4004300A at Import data to Table01, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Import data to Table01, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Import data to Table01, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Import data to Table01, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x402090DC at Import data to Table01, Flat File Source [1]: The processing of file "C:\import_files\sampledata.dat" has started.
Information: 0x4004300C at Import data to Table01, DTS.Pipeline: Execute phase is beginning.
Warning: 0x8020200F at Import data to Table01, Flat File Source [1]: There is a partial row at the end of the file.
Information: 0x402090DE at Import data to Table01, Flat File Source [1]: The total number of data rows processed for file "C:\import_files\sampledata.dat" is 0.
Information: 0x402090DF at Import data to Table01, OLE DB Destination [5467]: The final commit for the data insertion has started.
Information: 0x402090E0 at Import data to Table01, OLE DB Destination [5467]: The final commit for the data insertion has ended.
Information: 0x40043008 at Import data to Table01, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DD at Import data to Table01, Flat File Source [1]: The processing of file "C:\import_files\sampledata.dat" has ended.
Information: 0x40043009 at Import data to Table01, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Import data to Table01, DTS.Pipeline: "component "OLE DB Destination" (5467)" wrote 0 rows.
....so no records are imported, and there are warnings about partial rows, but the package completes successfully. When this package is fed a good input file, 80 characters per row, it imports as many rows as are in the file with no problem.
The strange thing is we have other SSIS packages that fail when variable-length rows are used as input. I've looked and compared those packages to this and for the life of me cannot see what they might be doing different.
If you have any ideas or leads I could follow I'd be grateful. Thanks!
