How to update Sharepoint List associated to a workflow with SSIS ?
Hi all, I've started a new SSIS project which needs to sync daily a SharePoint list into a SQL 2008 database. Every day the SP list is updated with new suppliers. It is associated to a validation workflow in order to get a clean list. My SSIS project is looking good so far, when I run it I'm able to get approved records from the SP list into my SQL table. But I need to go further. I need to tell to the SP list that the datatset I have at the end of my SSIS flow has been synced. I need to do that in order to have these records only once otherwise at the next run I'll get duplicates. So my idea was to go for a boolean field in the SP list and update this field and the end of my data flow. I'm deriving the colum in order to update that in my flow. I set all the records to IsSync == True But when I run the flow and I check the SP List...only the version field in the SP list has changed and nothing else! Any help would be appreciated ! Regards, Pierre
April 14th, 2010 11:44am

What does your package flow look like??? Are you setting the IsSync field to "True" once the SP list has made its way through to teh SQL table successfully? You might also need to see if the account running the package has access permissiosn to update the SP list via SSIS. Hope this helps. Cheers!! Muqadder.
Free Windows Admin Tool Kit Click here and download it now
April 14th, 2010 11:50am

Hi Muqadder, You made a really good point about when should I do the update in Sharepoint ! Right now I was doing that at the same time as the SQL insert...which is not good ! If something goes wront at the SQL side I'll still update the SP list Here is the current flow: http://i42.tinypic.com/t6qema.jpg I'm not familiar with how to change the account running the package...could you point to some directions ? Thanks a lot !
April 14th, 2010 12:12pm

I'd suggest you capture the sucsessgfully loaded rows into a relational table ( I guess you'r already doing that) in your first data flow task. Use a second data flow task immediately after the first one in your package flow to read from this SQL table as source and then Update the corresponding records in the Sharepoint list destination. Hope this helps. Cheers!! Muqadder.
Free Windows Admin Tool Kit Click here and download it now
April 14th, 2010 12:21pm

Well I tried your idea but it is still updating the version field only in the Sharepoint List and does not update the boolean field (IsSync)...it is correctly mapped though The Sharepoint system Account is the user used by SSIS. I don't understand because I can do update with this account in Sharepoint so it should be ok with the SSIS package also Mmmmmh I'm stuck there...
April 14th, 2010 1:54pm

OK I just discovered that I can update other fields without problems but boolean still does not update! I tried different data conversion but with no luck. So I'll work with another datatype for this IsSync field ^^ But if anybody knows why boolean does not update...I'll be happy to know why ! Cheers! P.
Free Windows Admin Tool Kit Click here and download it now
April 14th, 2010 3:10pm

Hi Pirerre, After research, I found the root cause for this issue. The issue is caused by the SharePointListAdapter does not handle boolean value in prcessing input. To fix the issue, please change the DataType of the input column "IsSync" to be "string [DT_STR]" to solve the issue. If you have any more questions, please feel free to ask. Thanks, Jin ChenJin Chen - MSFT
April 21st, 2010 6:06am

If you can use third-party solutions, check the commercial CozyRoc SharePoint Source and Destination components.SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
Free Windows Admin Tool Kit Click here and download it now
June 8th, 2010 4:37pm

HI Jin Can you please let me know how to update sharepoint list from ssis. i am struggling from many days but could not find any article describing as how to update sharepoint listSri
April 14th, 2011 1:13pm

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

Other recent topics Other recent topics