locked
Error Handling and Logging with Propagate as False in SSIS RRS feed

  • Question

  • Hi,

    I'm using a Foreach Loop Container with different tasks included like like,XML task,Execute SQL Task,Script Task

    Here the foreach container is used to flow through XML files in a folder.The requirement is that,even if i found any file corrupted,i just should log it and transfer the file to a FAILED folder and just continue the foreach with next file.

    For that i have set the Propagate Property of ErrorHandler Event as False,which is letting me continue even at the case of error.But my logging and moving the corrupted files to Failed folder is not happening...

    Please help with your valuable suggestions..


    Saturday, July 6, 2013 11:07 AM

Answers

  • Hi Soya,

    You can use Script task for validating the file,

    1. create a variable which holds flag or Boolean value which represents true or false for file validation,
    2. if it is true then process the file and if it is false move it to different folder; this can be cone by adding a precedence constraint with expression after script task. 
    3. Use dataflow task to map and transfer data from valid xml file to database.
    4. Inside the DFT use data conversion component for converting between datatypes.

    Regards

    Naveen

    • Edited by Naveen Kumar K.R Friday, July 12, 2013 8:48 AM More detailed Answer
    • Marked as answer by Mike Yin Tuesday, July 16, 2013 3:54 AM
    Friday, July 12, 2013 8:46 AM

All replies

  • Hi,

    Please share the screenshot of the package to give us a better understanding.

    Thanks and Regards,

    Shanoof Basheer

    -------------------------

    Please mark as answer or please vote helpful if you think the answer serves its purpose

    Saturday, July 6, 2013 12:04 PM
  • Hi Shanoof,

    I tried to insert the image,but am getting a weird error like this

    "Body text cannot contain images or links until we are able to verify your account."

    So the process is like this

    DataFlowTask -->ScriptTask-->ExecuteSQL-->FileSystemTask

    Please reply with yourvaluable suggestion.

    Sunday, July 7, 2013 5:59 PM
  • You can use SkyDrive or ImageShack to upload images Soya.

    But your issue is in not having the OnErrorp Precedence Constraint used to branch the execution to move the file and log the error.

    So, in short, your design should have been:

    DataFlowTask -->ScriptTask-->ExecuteSQL-->FileSystemTask

                          \->OnError Flow-->Log Error-->Move Bad File

    http://sqlserverselect.blogspot.ca/2010/12/ssis-foreach-loop-container-continue-on.html shows this design


    Arthur My Blog

    Monday, July 8, 2013 2:33 AM
  • I agree with Arthur, You must have on error precedence constraint(With dotted red connector in the picture below).


    Rajkumar

    Monday, July 8, 2013 5:14 AM
  • Hi Arthur and Rajkumar,

     Thanks for your reply.I did try the same with Failure Precedence ,but then i'm got an error like :

    [File System Task] Error: An error occurred with the following error message: "The process cannot access the file because it is being used by another process.".
    Is there any settings which i'm missing out here.

    Soya

     


    • Edited by Soya Mithun Monday, July 8, 2013 6:21 AM missed out a name
    Monday, July 8, 2013 6:15 AM
  • Hi Soya,

    Can you please paste a screen shot of the control flow?

    is there anything in the error handler of the DFT?


    Rajkumar


    • Edited by Rajkumar5055 Monday, July 8, 2013 8:57 AM Additional Info
    Monday, July 8, 2013 8:55 AM
  • Hi RajKumar,

    The below is my taskflow

    https://skydrive.live.com/?cid=819B793F619BF7BA&id=819B793F619BF7BA!107#cid=819B793F619BF7BA&id=819B793F619BF7BA!110

    Soya

    Monday, July 8, 2013 9:54 AM
  • Hi Soya,

    Please choose "Logical OR" for error precedence constraint(Double click the red link). Also please confirm if you have "On Error" Event handler on the DFT or Sequence container. If yes, what do they do?


    Rajkumar

    Monday, July 8, 2013 10:11 AM
  • Hi RajKumar,

      Made the Precedence constraint as 'OR' and the EventHandler of both DFT and Foreach Container is blank.The DFT contains:

    https://skydrive.live.com/?cid=819B793F619BF7BA&id=819B793F619BF7BA!107#cid=819B793F619BF7BA&id=819B793F619BF7BA!112

    Still the error remains same.

    Soya

    Monday, July 8, 2013 10:48 AM
  • Hi Soya,

    file in use error tells it all, you probably have an open handle to a file that you want to write to again.

    The image above seems unrelated though. Make sure the design is revised.

    If in doubt use FileMon to see what keeps the file locked.


    Arthur My Blog

    Tuesday, July 9, 2013 12:18 AM
  • Hi Arthur,

      The DFT includes an XML Task.Error occurs during the process of XML parsing.So does the file remain locked even after the error has occured.Do we have any option to release the file lock when the error has occured.

    Soya

    Tuesday, July 9, 2013 4:33 AM
  • Hi Soya,

    I suspect if the file is not immediately released by XML Source, Can you try closing the file using a script task before File system task where the file is moved to error folder?

    Check the below VB script

    http://stackoverflow.com/questions/6706312/how-to-check-if-the-text-file-is-open-and-close-the-text-file


    Rajkumar

    Tuesday, July 9, 2013 4:41 AM
  • Hi RajKumar,

     I tried in the script,when i tried to open the file,it goes to the catch block with an exception:"The process cannot access the file because it is being used by another process.".

    So i hope that means the filehandle is not released from the XMLTask at the error.

    I even tried to access the file outside the Foreach Container,but the same error prevails.So how can i continue?How can i acess the file to move it to the Failed directory.

    Soya

    Tuesday, July 9, 2013 7:51 AM
  • Hi Soya,

    Can we have an image of the entire package please?

    I suspect the error is due to you reading the XML file from several places.


    Arthur My Blog

    Thursday, July 11, 2013 8:05 PM
  • hi Arthur,

    the control Flow and Data Flow image is attached here:

    https://skydrive.live.com/?cid=819B793F619BF7BA&id=819B793F619BF7BA!112#cid=819B793F619BF7BA&id=819B793F619BF7BA!120

    Soya

    Friday, July 12, 2013 5:23 AM
  • Hi Arthur and Rajkumar,

       I'm using XML task to parse and shred my XML data from file into a DB table.Is it the prefered way or should that be done with a StoredProcedure.

    Please provide your feedback

    Soya.

    Friday, July 12, 2013 5:29 AM
  • Hi,

    What is the method or process you are following to find whether the XML file is corrupted or not.

    What is suggest is to write Script task , which contains a code to validate the file and based on that you can decide whether the file has to be taken for further processing or not. If file is known to be corrupted then you can write a code in the same script task to move to FAILED Folder and continue with next file.

    Regards

    Naveen

    Friday, July 12, 2013 6:28 AM
  • Hi Naveen,

      Thanks for your reply.

    Actually i'm using the XML source task.I believe, it checks the xml files with the schema provided as well as extracts the data as elements.

    As per your sugession,should we do this task using C# in script task and then how should we do the data conversion part(because XML data is of type [DT-WSTR],it needs to be changed to [DT-STR]) and the mapping part(where i map the XML filed to the table field)

    Soya

    Friday, July 12, 2013 8:33 AM
  • Hi Soya,

    You can use Script task for validating the file,

    1. create a variable which holds flag or Boolean value which represents true or false for file validation,
    2. if it is true then process the file and if it is false move it to different folder; this can be cone by adding a precedence constraint with expression after script task. 
    3. Use dataflow task to map and transfer data from valid xml file to database.
    4. Inside the DFT use data conversion component for converting between datatypes.

    Regards

    Naveen

    • Edited by Naveen Kumar K.R Friday, July 12, 2013 8:48 AM More detailed Answer
    • Marked as answer by Mike Yin Tuesday, July 16, 2013 3:54 AM
    Friday, July 12, 2013 8:46 AM
  • Hi All,

      I had sucessfully created the Package and it worked fine.But once the package was imported into the SQL 2012 Server,am getting an error

    The XML Source was unable to process the XML data. Object reference not set to an instance of an object. 

    in DataTaskFlow(in XML Source).

    Can any one help me with this.

    Wednesday, July 24, 2013 11:07 AM
  • Do you want to unmark the above as the answer?

    Or you want a new thread to resolve the new error?

    I suspect the XML was not the same, or how can I repro the issue?


    Arthur My Blog

    Tuesday, July 30, 2013 5:57 PM