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

I have been given a connection string which I am told will allow me to connect to a SharePoint list. I wish to connect using DTS in SQL and work with data in the sharepoint list. Can you advise how to set this up? I do not have much experience of DTS and am using wizards and cannot see where or how to use this connection info.
The Connection string : Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes; DATABASE=http://teamupeudev/CAEP/;LIST={08F43A76-41E2-4EE5-B4B6-207510F05F16};

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You are going to need that Data Provider installed to see it in DTS. I think this is it:
After that you can drag over the SQL Server connection and change your data provider to Microsoft.ACE.OLEDB.12.0
Here is the screenshot for editing the connection properties.
BriCogAuthor Commented:
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...

The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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}
BriCogAuthor Commented:
the {08F43A76-41E2-4EE5-B4B6-207510F05F16} is the GUID of the SharePoint list..

I will ask about the datasource
BriCogAuthor Commented:
Feedback from our SharePoint guys:
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.
First of all test your connection string, external to anything else:
1. Create a blank text file
2. Paste in your connection string:
3. Save the file
4. Rename the file extension to .UDL instead of .TXT
5. Double click it and test it
This verifies that its a valid string and it can connect.
If that is successful, you start up DTS and use a connection type of 'Microsoft Data Link' and then point it at your text file (.UDL file).
If you get that far, post back.
Note that neither DTS or UDL are recommended for new development. If you have the opportunity you should use SSIS (the version of DTS that comes with SQL 2005)
BriCogAuthor Commented:
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...
BriCogAuthor Commented:
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:
BriCogAuthor Commented:
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.

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.
BriCogAuthor Commented:
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.
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:
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.
BriCogAuthor Commented:
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.
BriCogAuthor Commented:
Cancelled my membership for now, thanks for the help.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.