How I can store the user id of the user who last modified an item in a list if the list is being modified every night by an SSIS script?

Question

I have a custom list in SharePoint 2010 that contains about a 1000 items. An SSIS script writes to this list every night and hence changes the "Modified" and "Modified By" columns at that time for all items. I want to store the value of "Modified" and "Modified By" columns in the list item if the item is updated by a regular user (not the user ID used by SSIS) or an admin. I tried using the "Me" technique to get the user name of the user making a change during the day but each night, as the "Modified By" column is updated, so will be the column using "Me".

Just to explain the technique I used, I created a column called Me and another column called "Last Modified By". Both were created as "Single line of text". I then set the default value of the "Last Modified By" column to be calculated and put in the following formula: =IF(AND(Me<>"DOMAIN\ssis-user",Me<>"DOMAIN\admin1",Me<>"DOMAIN\admin2",Me<>"DOMAIN\admin3"),Me,""). I then deleted the Me column so the "Me" in "Last Modified By" column now points to the default [Me] that gives the username of the person who modified the list item. In this way, I am able to get the user name of the user making an update to the list item, but it will be overwritten by the SSIS script user name overnight. I do not want it to be overwritten unless the user making the change is not the SSIS or one of the admins.

I have access to UI functionality or SharePoint Designer workflows to create this solution, although I would prefer to do this using without workflows if possible. All ideas are welcome.

Answer 1

Here is how I would approach this. (more likely choosing the workflow route)

Solution 1 : Create another column called XYZ or whatever "Manual Edit By:"...name is irrelevant.

Create a basic "Editing" triggered workflow.

make a condition that says IF (Modified By is not equal to AUTOMATED ACCOUNT) then -> Update the "Manual Edit By:" column to write the Modified by Users Name into the field.

This will make it so that only when an actual user edits the item it will write to the XYZ / Manual Edit Field.

I hope this makes sense to you. I have done this before and it works great ;) Let me know if I need to clarify more.

Solution 2: You could have a powershell script make a copy of the list and the last modified users prior to the SSIS script running, then after the SSIS script runs, overwrite the list with the "backed up" information via a mapping function triggered by the server to run from an event.

Answer 2

As I understand the problem, there are two solutions for this problem.

Solution 1: In case, if you dont need to track when the item got updated and who updated it and you are using SharePoint Farm solution to update the list item, then instead of using item.Update() use the method item.SystemUpdate(). The SystemUpdate method will update the item metadata without updating the context i.e. who updated it, and when it got update. This means it will retain the timestamp and editor information from the last modified user. Drawback of this is, you need to run code from the from the Site Collection admin account (or Application Pool account).

Solution 2: In case you want to track, when and what updated on item simply enable versioning on your list. So whenever the item get updated, a new version is created. You can enable moderation on top of it as well, so that approvers get notified when the content gets updated, review it and take action as required.