Redirect to the point where error occured

Question

Post reply

Redirect to the point where error occured

RexSmith

Ten Centuries

Points: 1027
More actions
April 15, 2010 at 6:51 am

#144109

I am reading flat files in SSIS and then processing them to the equivalent SQL server tables.
I have written OnError (event handler) for the foreach loop container
If there is any file with some errors, I dont want the code to loop out but to continue reading the next set of files and so on.
Can anyone give me hints to do this?
Rgds,
Seetesh
Rex Smith

Viewing 15 posts - 1 through 15 (of 17 total)

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

You might try setting the max error value in the F-E-L to a number higher than 1, try 5000..

CEWII

RexSmith Ten Centuries Points: 1027 More actions · Answer 2

My foreach loop container loops through a list of files say 100 of them.

If the error occurs in reading the 10th file I have to stop procees this 10th file and move on to the 11th file thats been read by the foreach loop container.

The same file will be ignored and moved on to another folder.

Its actually looping to the flow task in this case the "foreach loop container".

Rgds,

Seetesh

Rex Smith

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

Could you send screen shot of your package, I for the life of me can't visualize what you are telling me..

CEWII

RexSmith Ten Centuries Points: 1027 More actions · Answer 4

The dummy script task is created to redirect the flow to the foreach loop container. But I am not able to do so.

Rex Smith

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

What do you mean by "The dummy script task is created to redirect the flow to the foreach loop container" I don't see you needing to redirect it.

Look at these properties of the F-E-L and ALL components inside of it:

FailPackageOnFailure should be False

MaximumErrorCount should be higher than 1, choose a big number.

Also, on the connector from the dataflow to the scrip change the property LogicalAnd to True, I believe it is false.

See how those items help you.

CEWII

RexSmith Ten Centuries Points: 1027 More actions · Answer 6

I removed that dummy task but added a Sequence Generator now setting the MaximumErrorCount property of the container to 0 and made propogate value = false.

Something close what I need. I will upload the page soon after testing.

Rex Smith

RexSmith Ten Centuries Points: 1027 More actions · Answer 7

The foreach loop container doesnt iterate though the collection of files due to reasons like the file extension is not correct.

I want the container to stop processing and not proceed on to the next task after it.

Any clues?

Rex Smith

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 8

If the file extension is incorrect then you will likely get no files to process. In that case the F-E-L will complete successfully and move to the next component. I'm not sure if that answers your question..

CEWII

RexSmith Ten Centuries Points: 1027 More actions · Answer 9

In that case the F-E-L will complete successfully and move to the next component. I'm not sure if that answers your question..

Actually my requirement is not to move to the next component. I want to have to reroute to a Mail task of sending mail that the file extension criteria was not met for one or all the files in that foreach loop container.

Currently it does move to another component which is a SQL Task calling a Stored Proc in my case which gets invoked without any data in tables to be processed unnecessarily.

Any clues?

Rex Smith

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 10

I can't think of a way for that component to do that. If the file extension is wrong it will generate zero files, this could also happen if there are no files available. You might be able to use a script component that does a dir and a count of files and then fails if there are no files.

Thats all I got right now..

CEWII

RexSmith Ten Centuries Points: 1027 More actions · Answer 11

5 Pint of beer to you from my side for this solution. Working on it now to make it work else I will have nightmares if this code goes live without these patches (your solution).

Rex Smith

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 12

Elliott Whitlow

SSC Guru

Points: 102402

April 29, 2010 at 10:00 am

#1159854

Thanks. Let me know how it works out for you..

CEWII

RexSmith Ten Centuries Points: 1027 More actions · Answer 13

Yup. Will post my code for your reference.

Rex Smith

RexSmith Ten Centuries Points: 1027 More actions · Answer 14

I am writing a script that will delete the files on the local drive.

Dim path As String = "C:\aadelete"

Dim dateToday As Date = Now().AddDays(-1)

Dim filePart1 As String = "aa.txt"

Dim fullFileName As String = path & "\" & filePart1

System.IO.Directory.SetCurrentDirectory(path)

' System.IO.Directory.GetFiles()

If System.IO.File.Exists(fullFileName) Then

System.IO.File.Delete(fullFileName)

Dts.TaskResult = Dts.Results.Success

Else

Dts.TaskResult = Dts.Results.Failure

End If

But I want to delete all files in the folder.

Rex Smith