SSIS failed validation and returned validation status “VS_ISBROKEN”

5
votes

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.

6
sqlsql-servervalidationssis
What does your job look like ? – Erran Morad
So far I have fully declared the table to rule out it was finding the correct db and recreated the package due to reading that a change could corrupt the metadata. – epelletier9740
And what happened when you used a user table instead of a temp table? – Dave Cullum
If you're running into the same error using a user table, then we'll need to see your SSIS package (screen shots) so we can understand how you're creating the table, where its residing, and how you're inserting into it (show us the destination task). This is a good test, as originally my thought was because # tables are non-persistent (unless you configure SSIS to maintain your connection for you). – Dave Cullum
@epelletier9740 - You mean SSIS 2012 ? I'll make a guess. There is not really much else I can do without seeing the package. Have you set "RetainSameConnection" = True for the connection manager which lets you connect to the server/instance (NOT DB, that would be tempDB) where the temp table is created ? – Erran Morad

6 Answers

8
votes

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.

1
votes

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());
1
votes

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.

0
votes

I ended up solving the problem which was overloading tempDB. When I slowed the process down to one command to tempDb at a time it all ran through smoothly.

0
votes

I faced the same error, in my case, I was using SSIS to import data from an excel file into a couple of tables.

I used 2 differents files and it failed with one and worked with the other, after some review I found that I was referring to the name of the excel sheet within the package, so the excel sheet has to be named EXACTLY (i think it is case sensitive) as you used on the SSIS package

0
votes

I'm using VS 2017. I wonder if after a while it forgets your "saved" database passwords, because mine worked fine for days, then just quit working out of the blue, gave the VS_ISBROKEN error. After I reentered the password for one of my database connections--despite the fact I had checked the Save Password checkbox previously--it started working again.