i would like to know whether there is anyway to load the content of a Sharepoint(2007) List from a SQL server database. have tried as a workaround to make Access database(.acc db)as a source for the List so that when changes made to the .accdb, automatically it reflectes the sharepoint list. so i tried to make use of ssis 2005 to dts data from SQLserver db(Source 1) to .accdb(Destn1), then from Destn1 the SPList would receive data. the Object that accdb supports to SPList is of Sharepoint List type i am not able to DTS data from sql server to accdb. Any suggestions wld be appreciated!!!

Have you tried using the Sharepoint web service? You could read the values from your database, and use the Web Service task to call the Sharepoint web service to add list items.

I tried making use of Access 2007 and Choose link to data source by creating a linked table and redirecting it to a sharepoint list. i was able to expose the table structure to the list but the data contents of the Table was never shown in the list. if incase i need to take up the choice of web services is there any good links that would help me in completing this would be great!! Thanks

http://www.developer.com/tech/article.php/3104621

SharepointSource and Destination adapter for SSIS http://www.codeplex.com/SQLSrvIntegrationSrv/Release/ProjectReleases.aspx?ReleaseId=15424

here's a good walk through as well. This is what I used, when I had to import SharePoint data.

Well, you can use that - and that was the best option before, but using the source and destination adapter is a lot easier sine it fits in a flow natively, and will adjust the query to get only the columns you use to the next datapath. It also allows you to provide a CAML query when you need it, but by default will grab the items in a list effortlessly (as well as handle updates and deletes too)

yeah, the CodePlex samples look very promising, if you're using SQL 2008. Not necessarily a safe assumption that everyone has moved to SQL 2008 already.

Actually, you should be able to recompile them against 2005 with some code tweaking. I haven't tried it with those components, so I'm not positive, but I've moved a few of my own back and forth between versions without significant headaches.

I may have to investigate that more closely because we use SharePoint a lot, and it is a huge source of headaches. That may also prove to be a good incentive to upgrade our systems to a newer version than 2000. =D

That should be correct. The only thing that is version specific is the declaration of the SSIS Variables, which had to be based on the version. If you replace anything that ends in a *100 with a *90 and recompile (*80 for sql2000). Good luck and email if you run into any issues on the codeproject site where it is hosted.

I'm pretty sure *80 won't work, as the SSIS objects weren't available for 2000. Unless, of course,someone went through the trouble of porting all of them.

Haha, good point Time for a sql upgrade in that case.

My question here is regarding Sharepoint List Source and SSIS.When you drag the Sharepoint List Source to Data flow and double click on the Sharepoint List Source to get to the "Advanced Editor"under custom properties you have this CAMLQuery.When i paste my camlQuery e.g<Query xmlns="http://schemas.microsoft.com/sharepoint/soap/"> <Where> <Eq> <FieldRef Name="Created" /> <Value Type="DateTime"> <Today /> </Value> </Eq> </Where> <OrderBy> <FieldRef Name="ID" /> </OrderBy> </Query>I get error saying my XML isn't valid.Where should be pasting this code or how should it be used.I have also tried using the Property Expressions Editor but no luck...Any ideas?

Try removing the xmlns declaration.

KevinIdzi, No luck it doesn't work.There must be a way to use the CAMLQuery options.Guess thats why its there.I don't want to believe someone hasn't used this.

Well, I used it with one of my first samples internally in my group. I'll have to dig in and see what is wrong. Try picking it apart for now. The orderby is not needed (that's how it comes anyways).

Thanks Kevin, If you find a solutions pls do post it.The only way around this is to create a view in sharepoint list that does the filtering anf then use it in SSIS.Patrick

OK. First of all, the xlmns needs to go. That is a namespace which is not required, and I'm not using it - so I will not see anything in the query if that is used.Secondly, if you paste it in, then try copy/pasting it back out, I think you'll see the problem. The code you're pasting in has newlines, and the box your pasting it to doesn't seem to support that. It would be low pri for me to update it to support newlines, but you can always requst it on codeplex.Try this:<Query><Where><Eq><FieldRef Name="Created" /><Value Type="DateTime"><Today /></Value></Eq></Where></Query>

I get as far as the Feb 18 post, but am getting an Unauthorized error. Am I missing something in the setup of the task where credentials are supplied??

Depends on how you're running the task. The account executing the task need to have access to the list. So if you're running it from a SQL job, you need to setup a SQL Credential and job proxy to use a different account (unless you just run sql agent itself under the account).

Hi, The best way to may your CamlQuery works, is to use the expressions proprety expression. In the list of property select the [yoursharepointSource].[CamlQuery] In the expression copy and paste your query. Take care that you should change <Query><Where><Eq><FieldRef Name="Created" /><Value Type="DateTime"><Today /></Value></Eq></Where></Query> by "<Query><Where><Eq><FieldRef Name=\"Created\" /><Value Type=\"DateTime\"><Today /></Value></Eq></Where></Query>" Have fun

If you can use third-party solutions, check the commercial CozyRoc SharePoint integration. These are the relevant components: * SharePoint Source SSIS Data Flow Component * SharePoint Destination SSIS Data Flow Component * SharePoint SSIS Connection Manager Batch insert and update is also supported. SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

about this http://sqlsrvintegrationsrv.codeplex.com/ :)

about this http://sqlsrvintegrationsrv.codeplex.com/ :) Yes, it is similar. However the CozyRoc solution has the following extra features: - Doesn't depend on .NET 3.5 and LINQ. - Has separate SharePoint Connection Manager, which you can use to create your own scripts for working with SharePoint. - CozyRoc SharePoint Source includes nice designer for CAML queries. - Supports both SQL 2005 and 2008. - Includes nice installation, which takes care of the deployment.SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

For sql 2008, .net 3.5 / linq is a pre-req and installed by default. The linked version does support sql 2005/2008 and has a connection manager (see beta version) which supports alternate credentials. A MSI Installer is included which will install the necessary components to SQL Server for deployment purposes. However, the CozyRoc one provides an official support channel and a lot of other useful SSIS components. A CAML Editor sounds like a great addition as well. The U2U CAML Editor is what I would use, but it is nice to have it all together in one package.

Here is one more 3rd party solution for Sharepoint SSIS SharePoint Source Adapter - Reads Data From SharePoint List SSIS SharePoint Destination Adapter - Insert/Update/Delete Records from SharePoint List SSIS-Tutorials-FAQs | Convert DTS to SSIS | Document SSIS | SSIS Tasks | Real-time SSIS Monitoring

Dears, As explained before there is a component available on CodePlex with a great documentation about the usage : http://sqlsrvintegrationsrv.codeplex.com/wikipage?title=SharePoint%20List%20Adapters&referringTitle=Documentationhttp://sqlsrvintegrationsrv.codeplex.com/releases You can find also an external component included in the Reporting Services Connector : http://www.enesyssoftware.com/language/en-US/Products/EnesysISDataExtension/Overview.aspx Have a great day.Romelard Fabrice [MVP]