0

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.

  • Assuming the errors are related, "Disk was surprise removed" sounds like a hardware issue when the task was running. Especially if this is running during a window before other processes. Have you compared timestamps with the error and your backup jobs? Also, it seems like this process could be greatly simplified by doing a backup>restore rather than an SSIS package with numerous branches. – Jacob H Jul 10 '18 at 16:27
  • As far as order of the events with time, sysssislog has the package starting on 7/6 22:30:02.00, the DistributedCOM error event was thrown on 7/6 10:30:01 and 10:30:02, two warnings for 2 separate disk surprise removed event happened at 10:31:48. I have the control flow object to move to the next step on completion like small tables -> large tables -> delta only so the package error was finally thrown at 10:44:36 (all PM). – eparham7861 Jul 10 '18 at 16:35
  • For the restore, this is mainly due to me forgetting/still being kind of new to processes, but does that require that only those tables exist after the restore? target server drops its contents to receive the restore? The old server is technically owned by another team, and this team was thrown into a database where they did not have a lot of traffic and minor usage for their own process. Also, wouldn't I still need to force those destinations to either truncate or do a drop create before the restore? – eparham7861 Jul 10 '18 at 16:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.