OK, there are various ways to do this.
First of all, you can't use an OLEDB connection manager to get to SharePoint.
So, lets go over the various ways.
1) You can use the Cache manager in a dataflow, use the sharepoint source and load data you need into the cache manager, which you can then attach to the lookup component. That would be pretty slick (haven't done that), but would do what you need.
2) In the same dataflow, you could add a SP source and load the data in - and then Full merge to your SQL. The resulting dataset would have Null for the id in the SQL where the Sharepoint rows are old, Null for the id in the SP where the sql row is new,
and null in neither when it might be an update (i say update cause the data might be the same). A conditional component to check the fields of interest looking for 'differences' would cut down on your update rows to just those that are different. This is how
I tend to do it - but either way would do the job.
NOTE: Make sure when pulling from sql, you ISNULL(..,'') so that SSIS does not have nulls in the dataflow. Or else your conditional to check if a value in sql vs sharepoint will have to use the ISNULL function and it gets super messy. Better off just writing
the sql to get rid of nulls to make it just string comparisons.
So those are two different variances.
The connection manager has nothing to do with your scenario. The beta version has a connection manager to store the credentials of the user in which to make the call to SharePoint under. That's it.