SSIS Unexpectedly Fails

  • rcarmack

    Grasshopper

    Points: 12

    Greetings all!

    We have an data warehouse (DWH) that has been operational for a little over 5 years running on SQL Server 2012.  There is replication set up between the Production application's SQL Server database and our DWH server.   Recently we have encountered several errors that seem to be unexplained.  For example, on two separate occasions in different staging Data Flow Tasks, we suddenly encountered PK violations even though neither the Data Flow Tasks, nor the staging table structures had been changed in years.  A look at the replicated source data and the corresponding Production data indicated that the data hadn't been updated in several months or, in some cases, years.  In one case the Production data was bad and needed to be deleted, while in the other the Production data was correct and we needed to modify a view used in the Data Flow Task.

    So, the question is - why didn't the SSIS packages fail long before?  Has anyone encountered a similar situation?

    Thanks in advance for your help and suggestions!

  • Thom A

    SSC Guru

    Points: 98219

    rcarmack wrote:

    So, the question is - why didn't the SSIS packages fail long before?

    This might seem like a silly thing to say, but they didn't fail before because they didn't fail. Whatever it was they were doing, they didn't generate an error, or the error was set to not propagate upwards. Without having access to the package, knowledge of your system and your goals, the only thing I can really say is that the package wasn't designed correctly. It was most certainly doing what had been created  to do, but what it had been but what it had been created to do, and what it was supposed to do are entirely different things.

    The only person that can answer that question is you, or whomever else is supposed to be supported the packages. You have all of the information in front of you to find out, but you may well have to do (a lot of) digging before you find the answers you're looking for I'm afraid.

    If you find something specific you don't understand, however, then that's different. Perhaps you find some code in a script task/component and you're not sure it does what it says it does; then we can help you work out what it *does do*. But we can't give any answers for why a system we have no knowledge of, no access to, and no idea what it's supposed to and is doing on why it's doing what it is, and why it isn't doing what it isn't.

    Good luck though, and I'll be happy to try and answer any questions you have on specific parts you find that need further looking into.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

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

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