SQL Server 2012 - SSIS Execution Permissions

I ran into an issue where even the server administrator and sql server services accounts would not properly execute SSIS packages. I had to perform the following steps so the service account could execute a sql agent job and run a package.

Run Dcomcnfg.exe. Dcomcnfg.exe provides a user interface for modifying certain settings in the registry. In the Component Services dialog, expand the Component Services > Computers > My Computer > DCOM Config node. Right-click Microsoft SQL Server Integration Services 11.0, and then click Properties. On the Security tab, click Edit in the Launch and Activation Permissions area. Add users and assign appropriate permissions, and then click Ok. Repeat steps 4 - 5 for Access Permissions. Restart SQL Server Management Studio. Restart the Integration Services Service. (Source MSDN)

http://stackoverflow.com/questions/13941998/sql-server-2012-integration-services-failed-when-connecting-thru-ssms

SSIS - Excel Querying

The power and flexibility of SSIS never ceases to amaze me. With just a few simple lines of code you can import and manipulate data straight out of excel.

This piece goes on to the actual excel connection provider.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\<FILENAME.xls>;Extended Properties="EXCEL 8.0;HDR=YES;IMEX=1";

Instead of table loading the excel file on the datasource, utlize a SQL query such as this.

SELECT F1,F18 from [Sheet1$A3:D8]

WHERE 

  F1 IS NOT NULL

  ........