SSIS failed validation and returned validation status "VS_ISBROKEN"

Question

I'm trying to create a temp table and process two data flows using the temp table. It is in a sequence container and if I just execute the container it run perfect but when the entire package is ran it returns this error:

Information: 0x4004300A at V-AccidentCodesBase, SSIS.Pipeline: Validation phase is beginning.

Error: 0xC0202009 at V-AccidentCodesBase, Insert into Temp Table [69]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.".

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E14 Description: "Invalid object name '##TmpAccidentCode'.".

Error: 0xC004706B at V-AccidentCodesBase, SSIS.Pipeline: "Insert into Temp Table" failed validation and returned validation status "VS_ISBROKEN".

Error: 0xC004700C at V-AccidentCodesBase, SSIS.Pipeline: One or more component failed validation.

Error: 0xC0024107 at V-AccidentCodesBase: There were errors during task validation.

Answer 1

I would set the DelayValidation property to True. You may get away with just setting this on the Sequence Container, or you may need to repeat that setting on child objects, e.g. your Data Flow Task.

Answer 2

As other guys mentioned, the error may happen due to different reasons. In my case, I realized that I have tried to convert some NULL to int in Script section of SSIS. Something like :

ProductsBuffer.ProductId = Int64.Parse(reader["ProductId"].ToString());

so the fix was easy. I just checked the field before converting, to make sure it is not null:

 if (reader["ProductId"] != DBNull.Value)
            ProductsBuffer.ProductId = Int64.Parse(reader["ProductId"].ToString());

Answer 3

Also faced the same error message. The issue was permissions on the database for the user that runs the ETL (a service account). Make sure the user that runs the package has enough permissions to execute the query.

Answer 4

In my case I switched from OleDB to SQNCLI and it worked :/