Excel Source SSIS

Question

I have an SSIS package with an Excel Source reads an Excel table. I currently am using the Table or View Data Access Mode and it is literally reading every row in the worksheet, 1,048,576 which is the maximum.

The source worksheet has an Excel table on it named PSA_DATA. Why isn't this table in the Table or View drop down? There is an option for the worksheet followed by _FilterDatabase but this fails when I run the package even though it pulls the correct data when I press Preview. Wouldn't this make more sense than using the SQL Command and SELECT * FROM [fact_PSA$Ax:Bx]? The whole reason we use Named Ranges and Tables in Excel is because they are dynamic! Now I have to hard code the range in every time with rows numbers?

What am I missing here? Is there an easier way I am missing? I just want to move an Excel table into a SQL table! Why don't doesn't the most ubiquitous piece of software in the world easily talk to the second most ubiquitous piece of software in the world!?!?!

Answer 1

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.

Answer 2

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/