SSIS Package won't fail, even with bad input data

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!

---

Also another fix is: Insert a SET NOCOUNT ON statement at the start of the SQL command

I found this out at: http://louiebao.net/ssis-package-succeeded-without-errors-but-wrote-0-rows/

---

Ok, we've got this one figured out. What happened is the final column in each row expects a CR/LF ColumnDelimiter.

So when the first row is processed and comes up short, SSIS doesn't acknowledge the CR/LF simply because it comes too soon. It instead continues on and processes the second row as part of the first. In this case the second row is all that's left and we get the truncation message referred to above, because a CR/LF is never encountered at character 80.

In the case of the other SSIS package that does fail, a number of rows are close enough to the expected line width that when the package tries stuffing them into a single field a truncation error on that field is triggered.