Row Count task in data flow fails with "unspecified error"

  • Hi all,

    I have a data flow task with:

    Flat File Source (csv) -> Conditional Split -> Row Count -> Data Conversion -> Destination (OleDB)

    Everithing works in development environment, on development server, UAT server. On production it fails with the following:

    "Process file:Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Transaction record count" (2331) failed with error code 0x80004005 while processing input "Row Count Input 1" (2333). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

    Process file:Error: Unspecified error"

    No more information, the SSIS build verion is the same on all environments. The error appeared a few days ago, it was working for years.

    Any idea?

    Thanks

  • As a random guess - I'd say your CSV has some invalid data in it such as a row with an extra/missing comma. This can happen when your CSV allows for free text input in any field as people can type in whatever they want and it MAY contain a comma.

    Finding a bad comma is tricky though, but there are ways to do it - there are online tools to validate a CSV (search for "validate CSV file online") or offline tools you can download, or you can throw it at an AI to validate (but I don't always trust their results).

    My guess is that there is a problem with the input file. It could even be that the last row is a blank line - I've had CSV files end with an empty line and had that break things for me.

    If this worked for years and only broke recently, I'd be curious if it succeeds with an older CSV file. That would be the best way to determine if the problem is with the CSV file or SSIS.

    Another reason SSIS can fail is if it runs out of memory. SSIS operates in separate memory space from SQL Server, so it is EASY to have SSIS run out of memory, especially if the system hasn't been rebooted in a long time. Offhand, I think SSIS will throw an OutOfMemoryException not an "Unspecified error", but I've been wrong before.

    Another reason it may fail is if the file is locked by some other process (such as if it is being scanned by the antivirus or is open in Excel) or SSIS has no access to the file for some reason.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Viewing 2 posts - 1 through 2 (of 2 total)

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