SSIS package fails after column order changes

Question

I have a SSIS package that does a simple read from a flat file and inserts into SQL Server 2005. It was running fine on one computer (computer 1) with both the source and destination pointing locally.

The package then got moved to another computer (computer 2), again with everything pointing locally, and started failing. After looking into this for a while, it turned out to be that the columns of the destination table were in a different order on the two machines. The package was failing because it was trying to write data to the wrong table columns. That is, on computer 1 the columns were A,B,C and on computer 2 they were C,A,B - the package was trying to write A's data into C on computer 2, etc.

Am I missing something here? Does SSIS really depend on column order when writing to an OLE DB destination, instead of the column names? Or do I have a bad setting?

Answer 1

Have you checked the column mapping of in the data flow task? Sometimes the mapping disappears or points to a wrong column. In your case the SSIS package is moved then there is good chance the mapping is corrupted. I think you just need to correct the mapping and it should be a fix.

Answer 2

SSIS reads the metadata from the connection and stores it with the mapping. Sometimes it can detect a change and will give a validation error (in which case the package will fail at validation and you will have to alter the package to correct the problem). You can sometimes see this in the designer if you open the package, and it will offer to correct the columns.

In some cases, it will not detect changes during the validation phase, and it will fail during the insert.

So my question is, did it fail during validation or a later execution phase?