Sql-server – “Unspecified Error” when executing SSIS Package

sql serversql-server-2012ssis

I am executing an "Initial Load" SSIS package, which truncates all ETL tables first, then re-populates them by pulling data from a remote server. I get the dreaded "Unspecified Error" at a different point each time, and different ETL subflow each time. Other times, but rarely, everything runs fine. I have no idea how to get more information or where to even look.

The Server running the package is a Intel Xeon E5-2640 @ 2.50 GHz with 144 GB ram. (HP Proliant DL380p Gen8)
It's running Windows 2012 Server Standard.
Software versions:
SQL Server 2012
Microsoft SQL Server Management Studio 11.0.5058.0
.Net Framework 4.0
Operating System: 6.2.9200

Connection to the remote server is done through OLEDB:
Connection String: Data Source=MyServerURL;User ID=MyDWTransferUser;Initial Catalog=DBName;Provider=SQLNCLI11.1;Persist Security Info=True;Auto Translate=False;

See screenshot of the failure attached. enter image description here

Previously, I was receiving "Communication Link Failure" error. The internet connection was upgraded, and also we installed SP2 on SQL 2012 on this DW server. Now receiving "Unspecified Error" on this package, after various times of execution, on a different sub process each time. So it's not an SQL issue, but a configuration issue.

Any tips appreciated.

Best Answer

I was not able to get a more specific error message than: "Unspecified Error".

I solved the problem by improving the performance of the queries used.

There was a particular expensive proc which took 10 minutes to run. It joined 8 tables, one of which had 13m rows, had 5 where clauses, and produced 13 million rows. It was being run almost 20 times in the course of the various SSIS sub-packages. Sometimes it would be run 2-3 times in a single package, producing the same set of results, but obviously table-scanning each time.

So I created a table to store the results of that expensive query, made a scheduled job that runs daily at midnight to populate it once. Then i just joined the results of that table in each place where previously the expensive proc was being run.

Now the job runs in 25 minutes consistently (instead of 3 hours), and i haven't seen a single failure in the so far 8 runs it executed.

So I suspect that the under-the-hood issue with the "Unspecified Error" was an out of memory exception that may or may not have been linked to the fact that the query was being executed over VPN linked server connection.

Related Question