Excel Source SSIS

If the sheet name is not shown in Table or view combobox, it is not a bad idea to use a Sql Command.

But When using SQL Comand to read from excel it is not necessary to specify a range, OLEDB will take used range by default just use the following command

SELECT * FROM [fact_PSA$]

Workaround

you can try reading your excel file from a script task or a script component, you can follow one of the following links to achieve this:

• https://social.msdn.microsoft.com/Forums/sqlserver/en-US/2d45f180-9fd0-4224-a298-cb99e2b2100a/how-to-read-the-contents-of-excel-file-through-ssis-script-task-without-the-headers?forum=sqlintegrationservices
• https://msdn.microsoft.com/en-us/library/ms403358.aspx
• http://billfellows.blogspot.com/2013/04/ssis-excel-source-via-script.html

Side Note: there are many links you can follow to import data from excel to SQL using SSIS:

• http://www.sqlshack.com/using-ssis-packages-import-ms-excel-data-database/
• https://www.mssqltips.com/sqlservertip/2770/importing-data-from-excel-using-ssis--part-1/
• https://www.simple-talk.com/sql/ssis/moving-data-from-excel-to-sql-server-10-steps-to-follow/
• https://www.simple-talk.com/sql/ssis/importing-excel-data-into-sql-server-via-ssis-questions-you-were-too-shy-to-ask/

I appreciate the links to work-arounds, but I didn't really get an answer to my question. Why can't we reference an EXCEL TABLE (not a worksheet) from the SSIS Excel Source???

I ended up using the SQL Command data access mode with this query:

SELECT * FROM [fact_PSA$A:W]
WHERE fact_PSA_ID IS NOT NULL

Somehow, using SQL stopped it from reading every possible row in the worksheet even though the range provided is set for "A:W" which is every row. I guess the "WHERE fact_PSA_ID" limits the rows read before it hits the SSIS source.