July 15, 2009 at 4:56 am
Hi,
I am importing several excel files using a ForEach loop container. Each excel file has several thousand rows, which need to be validated through the load process. Each row is checked for the following
1. Data conversion can be carried out i.e. has the excel data been supplied in the correct format
2. Customer Lookup - are there any erroneous customer refs which do not match source data
3. Product Lookup - are there any erroneous product refs which do not match source data
These are three different steps in the load process, but a design restriction is that if one row fails, the whole excel file has to be rejected / redirected to the error output.
I have redirected rows which do not match the correct criteria, but there doesn't seem to be a way of rejecting the whole source file.
Is there a simple method of doing this before I start looking for something too complicated?
July 15, 2009 at 9:12 am
Use the event handler OnError and in there have your Source Excel and Error Output connection mapped. That should do it.
December 9, 2009 at 8:57 am
Sorry about the late reply to this....just getting the project going again 🙂
How do I place the on Event Error handler to reject the files for a particular task?
The whole Data Flow Task is in a For Each Loop and therefore the filename is variable as it's looping through. If the lookup has an error, instead of re-redirecting the error row, I would need then file to be skipped and the next file to be imported...
thx
December 9, 2009 at 9:35 am
[font="Comic Sans MS"]
Hi Jason,
Is the reject file format and source file format different for you?
If not - there is a quick solution - you can have a file system task (say FST ) to copy/move/rename the source file -> rejectfile.
Point the failure precedence constraint from those 3 validation task to the filesystem task (FST) and set the properties of those 3 precedence constraint to be 'logical OR'. They will appear as dotted red line ..
Let me know if this works for you 😉
[/font]
[font="Comic Sans MS"]--
Sabya[/font]
December 10, 2009 at 2:39 am
Thx Sabya, that is starting to work...
One thing though...As I am looping through files, lets say 10 excel source files, the 2nd one fails. The Lookup component in my Data Flow Task fails, the File System Task takes over and the rejected source file then moved to the "Failed" folder.
The For Each Loop then fails when trying to read to the 3rd file in the loop, as it still has a failed component from the previous file import.... How can I reset this to make sure once the rejected file has been moved, the failed component (i.e. the lookup) can be reset to run the Data Flow Task again without a problem?
Thx again... been a great help so far
December 10, 2009 at 3:50 am
Right....a bit of Googling and I got it to work.
I set the ForceexecutionResult propert of the ForEachLoop container to Success, then the container loops through the files and only imports the successful ones...
Great stuff.
A little more modding to see if I can identify the invalid rows in the failed files, then I am there.
Thx a lot..
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply