What are some Pros and Cons of ETL from Excel into my SQL Server DW?

I have a data model for my DW and the data source for this DW is Excel, i.e., there's no operational data source, all the data is in several Excel files, updated weekly. What are some things to keep in mind if I have to ETL using SSIS packages into my data warehouse?

Your biggest problem will be locking down the format of the sheets. Getting a lot of garbage data would be second on my list.

I agree with ngalemmo's first two.
Third on my list would be accessing files (typically on a secured file share) from the ETL process (typically running under a service account on a different server).
Fourth on my list would be file locking. If someone leaves an Excel window open (or has an Excel crash) you usually find your SSIS process fails.

Overall in my experience it can be done, but you need to put serious thought into the design and build of your control flow and data staging to make it as resilient as possible.

Good luck!