How do I use OLEDB to connect to a SharePoint List from MS SQL using DTS?

Here is the screenshot for editing the connection properties.
Microsoft.ACE.OLEDB.bmp

Thanks, I have installed the drviers and I can now select the data source you advise. From the connection string I have been given, how do I complete the connection properties? What goes in the Data Source field?

Thanks again, I appreciate this is real basic stuff...

ConnProp.JPG

Whoever provided the connection string should be able to give you the Data Source, which is what this provider requires. You may also need to make changes to Extended Properties in the All tab. This value is generated by MS and probably doesn't mean anything to DTS at this point. {08F43A76-41E2-4EE5-B4B6-207510F05F16}

the {08F43A76-41E2-4EE5-B4B6-207510F05F16} is the GUID of the SharePoint list..

I will ask about the datasource

Hi,
Feedback from our SharePoint guys:
Quote
The data part should be http://teamupeudev/CAEP/;LIST={08F43A76-41E2-4EE5-B4B6-207510F05F16};. And you need to use windows authentication.
I am not sure about this, as we have never used this type of connection before in our development work. We used the connection string in the default APIs provided by OLEDB class of .NET Framework.
EndQuote

Hi,
I have tried as you suggested, but get an error on step 5.,  attached: "...File Cannot be opened. Ensure it is a valid Data Link file'.
I guess I need to query this with whoever provided it?
I can access the ShaePoint site and can see that the GUID is correct...

I am trying this from a SQL server 2000 that also has a front end that is not yet compatible with 2005. So it will not be 2005 for some time...

attached...
UDL-error.JPG

I get the same error but I thinks its because the driver won't install in Vista x64.
Basically you've got to be able to see that provider listed in the first page (The 'Provider' tab) of the data link properties. I think you have commented earlier that you can actually see it in the provider tab? Can you screenshot it and post it up?
 
Asuuming that in your screenshot above you have indeed selected the ACE provider in the first tab, I assume that
Data Source = http://teamupeudev/CAEP/
Catalog = {08F43A76-41E2-4EE5-B4B6-207510F05F16}
 
This page at least verifies that the connection string is correct:
http://www.connectionstrings.com/default.aspx?carrier=sharepoint

Hi,
Sorry for late reply...

See SP1 & 2, I believe this is the correct provider.

In SP3 I enter as the data source:
DATABASE=http://teamupeudev/CAEP/; LIST= {08F43A76-41E2-4EE5-B4B6-207510F05F16};

I cannot select NT as it is greyed out.
When I key in my domain\userid & pass , and test connection I get error, SP4: 'Not valid accout...'
These are my network credentials and can browse the list normally through sharepoint.

SP1.JPG
SP2.JPG
SP3.JPG
SP4.JPG

Just one more thing to try: don't put the DATABASE= part into the field.
Then press OK and then open your .UDL file in notepad and see what it looks like. Can you post it here also as I'm interested to see what it comes up with. It should be roughly similar to what you were given by your developer team.
You may need to make some manual changes to it to get it to work.
 
To summarise, there are two simple ways to build and test a connection string:
1. Type the connection string into notepad, save as .UDL and open and test it.
2. Create a blank .UDL, open it and fill in the fields
 
Option 1 doesn't work because we get a "File Cannot be opened. Ensure it is a valid Data Link file" error. From the net this seems to imply that the driver isn't installed properly or is inaccessible, though I have no doubt that you have installed it properly.
Option 2 doesn't work because the input fields don't match what is in the required connection string - the connection string does not require credential information, but the UDL window does.

Hi,
Thanks for your efforts here.

I have not been able to save the set up due to the errors. In my previous posting I got the 'Not vlaid filename' when using the 'Test Connection' option. This is all within DTS.
When I try to save the Datalink either with or without the DATABASE= part, I get the 2 errors attached (SP5 & SP6) when I click OK, OK.  I cannot save the details I have entered. These are with my valid network credentials. When I leave it as default (Admin with blank password) I get SP5 and then SP7.

Now I don't know if this has any relevance, but I've tried using Microsoft OLE DB Provider for Internet Publishing with datasource http://teamupeudev/CAEP/;LIST={08F43A76-41E2-4EE5-B4B6-207510F05F16};. When I use 'test connection' it suceeds and I can save it. I have no idea what this indicates though... See SP8.
SP5.JPG
SP6.JPG
SP7.JPG
SP8.JPG

For the SP5 error, you could open the UDL file on notepad and delete the 'persist security info' element and try again. The UDL editor will probably put it back in though. Could you paste the contents of the UDL file up in a comment?
Regarding Microsoft OLE DB Provider for Internet Publishing, that actually sounds like a much more likely driver to work. Can you actually select data from that one if you use it in DTS?
 
Just to confuse things further, these posts indicate that it is the ACE driver which should be used:
http://cosier.wordpress.com/2008/11/19/connect-to-a-sharepoint-list-via-ole-db/
http://www.connectionstrings.com/?carrier=sharepoint
Which is bizzare because ACE is used for connecting to MS Access and Excel, not SQL Server.
Sorry I hope I'm not adding to the confusion here.
 

Will post more, maybe next year...I am travelling for the next 2 weeks and then the holdiays...

With Inter. Pub. the conenction tests ok, but the subsequent select fails (will post error next time...)

More confusion I can handle, and would hardly notice...

hehe enjoy your travels.