SSIS: Using Event Viewer to Trace Errors of A Package

**Overview** A package was developed due to a planned database server migration, and other teams with a dependency on legacy cross-database queries. There is a policy of not having Linked Servers, so an SSIS package was setup to push the latest data to the required tables on the old server. There was a restriction defined for this new package, where it will need to finish before a backup to a read-only server that is set to run nightly against the old server. This read-only server allows for getting data without many hits to the PROD database server, and the cross-database queries do not impact mission-critical processes. In case there are missed tables, some dynamic functionality was put in place to limit repetitive code/query changes. There are still scenarios that will require a physical change, but I will branch on that further down. I am going to start at the dynamic functionality, but I cannot confirm that the error is explicitly occurring there. I have two items that are attempts at being dynamic. The first loops over a lookup table to truncate the destination tables using a dataflow with a source and OLE DB Command. The second will loop over the same lookup table to define the tables in code and utilize SQLBulkCopy via a Script Task. The first hurdle tackled was dealing with large datasets. Large meaning size of data. Some examples are XML columns or poorly structured tables containing duplicate information. (For a table with the XML column, the column contains 2 to 4 GB alone) For those large datasets, they were pieced out of the dynamic SQLBulkCopy by using a normal data flow task in SSIS. A flag was added to the lookup table to determine the tables to exclude. For the table with an XML column, this was still fairly slow, so it was determined to only insert the delta and perform an update for changed rows. All that to try saying the context of the package that produced an error. **Issue** Exception handling was not defined, since data was not being transformed to some new data. It was essentially a normally backup process without creating a backup file. An error did occur, but it only showed a message of "Exception has been thrown by the target of an invocation.". We are looking into adding exception handling, since that would allow us to get a better error message. The confusing part begins that following day. The package ran successfully, and the way the package is setup picked up the missing data. No changes to the physical package, and data is only ever added or updated to the main tables. (no deletion of records) In an attempt to find a root cause, I am trying to step through Event Viewer, where the packages are deployed and DTEXEC exists. I have found the event that states the package failing, but there are no clear events surrounding that timeframe to see a pattern of the failure. Here are the events that I have found, but all may not actually apply: - Application - VSS: Event Id 12289; Volume Shadow Copy Service Error - SQLISPackage130: Event Id 12291; Package 'MyPackage' failed. - System - DistributedCOM: Event Id 10016; application-specific permission settings do not grant Local Activation Permission - disk: Disk 2 was surprise removed **Questions** Before I go further down this rabbit hole, is this even a viable way to trace that generic of an error message for SSIS? If it is, then do any of the above events state anything that I could be missing? As far as it throwing an error one day then not throwing error for the subsequent week (happened last week), is this throwing a flag or smell that I may not be understanding? Lastly, is there another way to trace this kind of issue that I have not stated above? Again, there is no exception handling in the Script Task, so the available exception message at that level appears to be lost. Admittedly, this might be something to let go and add the exception handling to trace down the road, if it happens again. Please let me know if I need to clarify anything, or if I am having a misconception on any of the above.