SSIS

Question

SSIS

HBK_4700

SSCrazy Eights

Points: 8932
More actions
April 18, 2017 at 2:19 pm

#342522

we have a SSIS package (Created in SQL 2014 with DATA TOOLS VS 2013)
Dataflow task to load the excel sheet data to sql database
added precedence constraint for success to move the file to archive folder and rename to current data time (For each loop container -file system task)
added precedence constraint for failure to move the file to error folder (For each loop container - file system task)
It's working fine for success.
but for failure it is throwing below error at dataflow task and not going to next step on failure (moving file to error folder)
How to fix the precedence constraint editor to achieve this? i want to send the file to error folder for even DFT validation errors
TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Data Flow Task 1 [Source - Sheet1$ [66]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.
Error at Data Flow Task 1 [Source - Sheet1$ [66]]: Opening a rowset for "Sheet1$" failed. Check that the object exists in the database.
Error at Data Flow Task 1 [SSIS.Pipeline]: "Source - Sheet1$" failed validation and returned validation status "VS_ISBROKEN".
Error at Data Flow Task 1 [SSIS.Pipeline]: One or more component failed validation.
Error at Data Flow Task 1: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)
------------------------------
BUTTONS:
OK
------------------------------

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

This is a validation error, not an execution error. To avoid the validation error and have the error thrown at execution time, try setting the Excel connection's DelayValidation property to true.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

HBK_4700 SSCrazy Eights Points: 8932 More actions · Answer 2

Thanks!!
Tried setting the Source Excel connection's DelayValidation property to true. No luck. DFT is failing with validation errors but not moving to next step ( move the excel file to error folder on failure precedence constraint)
also tried setting destination connection OLEDB & package properties DelayValidation property to true. Still NO luck

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

HBK_4700 - Tuesday, April 18, 2017 3:26 PM
Thanks!!
Tried setting the Source Excel connection's DelayValidation property to true. No luck. DFT is failing with validation errors but not moving to next step ( move the excel file to error folder on failure precedence constraint)
also tried setting destination connection OLEDB & package properties DelayValidation property to true. Still NO luck

OK, sorry about that. It can get a bit fiddly, as there are lots of options. Try setting ValidateExternalMetadata to false for your Excel source (in the data flow).

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

HBK_4700 SSCrazy Eights Points: 8932 More actions · Answer 4

I had setup the ValidateExternalMetadata to false for Excel source in the data flow and ran the package. Still it stuck at data flow task with validation errors not moving to next step. I think issue is with precedence constraint editor. I choose constraint and value as failure.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

HBK_4700 - Tuesday, April 18, 2017 4:03 PM
I had setup the ValidateExternalMetadata to false for Excel source in the data flow and ran the package. Still it stuck at data flow task with validation errors not moving to next step. I think issue is with precedence constraint editor. I choose constraint and value as failure.

I don't think so.
Validation is a kind of pre-execute phase. I suspect that your package is failing before it starts to execute.
But I've been wrong before. Try changing the constraint to OnCompletion and see whether it gets executed. If not, I think it proves I am right.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

HBK_4700 SSCrazy Eights Points: 8932 More actions · Answer 6

Disabled the onsuccess filesystem task. And changed onfailure constraint to OnCompletion. Package is not executed it just failed at dataflow task...Not moved to filesystem task which will move the excel file to error folder.

Yes. dataflow task is failing before it starts execute throwing validation error.

More details:

I wanted to check onfailure constraint. Removed one column name and data from excel or removed one column from sql table and trying to test it. but dataflow task failing with validation error ( which is expected since i made changes to excel) but it is not moving to next step on failure. How to update dataflow task and or failure constraint to achieve this. If i execute filesystem task separately it is getting executed and moving the file to error folder. so NO issue with filesystem task.