SSIS and Sharepoint List
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!!!
April 28th, 2007 9:45am

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.
Free Windows Admin Tool Kit Click here and download it now
April 29th, 2007 4:08am

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
April 30th, 2007 9:00am

http://www.developer.com/tech/article.php/3104621
Free Windows Admin Tool Kit Click here and download it now
April 30th, 2007 3:54pm

SharepointSource and Destination adapter for SSIS http://www.codeplex.com/SQLSrvIntegrationSrv/Release/ProjectReleases.aspx?ReleaseId=15424
August 20th, 2008 11:25pm

here's a good walk through as well. This is what I used, when I had to import SharePoint data.
Free Windows Admin Tool Kit Click here and download it now
August 20th, 2008 11:49pm

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)
August 21st, 2008 6:49am

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.
Free Windows Admin Tool Kit Click here and download it now
August 21st, 2008 6:58am

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.
August 21st, 2008 6:22pm

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
Free Windows Admin Tool Kit Click here and download it now
August 21st, 2008 6:40pm

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.
August 25th, 2008 6:47pm

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.
Free Windows Admin Tool Kit Click here and download it now
August 25th, 2008 8:28pm

Haha, good point Time for a sql upgrade in that case.
August 25th, 2008 8:49pm

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?
Free Windows Admin Tool Kit Click here and download it now
February 17th, 2009 3:16am

Try removing the xmlns declaration.
February 17th, 2009 7:08am

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.
Free Windows Admin Tool Kit Click here and download it now
February 17th, 2009 9:13am

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).
February 17th, 2009 6:05pm

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
Free Windows Admin Tool Kit Click here and download it now
February 18th, 2009 4:36am

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>
February 18th, 2009 8:52pm

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??
Free Windows Admin Tool Kit Click here and download it now
July 28th, 2009 6:56pm

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).
July 30th, 2009 7:26am

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
Free Windows Admin Tool Kit Click here and download it now
April 9th, 2010 6:37pm

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/
June 9th, 2010 1:44am

about this http://sqlsrvintegrationsrv.codeplex.com/ :)
Free Windows Admin Tool Kit Click here and download it now
June 9th, 2010 3:43am

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/
June 9th, 2010 5:38am

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.
Free Windows Admin Tool Kit Click here and download it now
June 9th, 2010 7:12am

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
May 2nd, 2011 5:40pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics