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;
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.