We recently needed to bring data from a Sharepoint list in to SSIS for use in an attribute lookup for a cube dimension. Seems like this should be pretty straight forward since SSRS does it natively now, but no - that wizardry hasn't made its way over to the SSIS team yet. As stated before, we don't care for third party dependencies or external non-standard assemblies of any sort in our packages. That means, as usual, we'll be writing a script component to take care of getting our data out of Sharepoint.
Of course we want to do this, it's a fundamental requirement. Dear Sharepoint, please refresh my PowerPivot when the data is ready, not on some arbitrary schedule. Until this functionality is built-in, people will continue to hack away at it to figure out how to make this happen. Is this supported? Certainly not - so continue at your own risk.
So the method we'll be using here is just to mimic what Sharepoint does when you check the box to "Also refresh as soon as possible" in the schedule configuration page. To accomplish this we open the profiler, connect it to whatever instance our Sharepoint PowerPivot database is hosted on and filter you TextData to "%Schedule%" or filter to just the Sharepoint PowerPivot database (SP2010_PowerPivot_Service_Application in our case) - then open up your test PowerPivot schedule configuration, check the box, click OK, wait for the schedule history to come back up and then stop the trace. Now you know you've got what you need, you just have to find it:
SELECT DirName, LeafName, tp_Email, CheckoutDate, IsCheckoutToLocal FROM AllDocs d WITH(nolock) INNER JOIN Webs w WITH(nolock) ON d.WebId = w.Id INNER JOIN Sites s WITH(nolock) ON w.SiteId = s.Id INNER JOIN UserInfo u WITH(nolock) ON (s.Id=u.tp_SiteID AND u.tp_ID=CheckoutUserId) WHERE d.Type <> 1 AND (LeafName LIKE '%.xls' OR LeafName LIKE '%.xlsx') AND (LeafName NOT LIKE '%template%') AND DeleteTRansactionId = CAST(0 AS VARBINARY) AND IsCurrentVersion = 1 AND IsCheckoutToLocal = 1 ORDER BY tp_Email ASC