Import utility for oracle server.

Question

Hi All,

I am working on an appraisal solution. Employee data is coming from Oracle table. For each record in table, it should be exactly one list item in custom list. There are around 1000 records. During import for each record

1. There are bit complex logics to calculate couple of columns values.

2. There are around 5 Active Directory hit to lookup the User Ids.

3. All the 5 users have to be added in suitable groups.

4. Appropriate permissions have to be given on list item too there is a complex logic behind.

If I would consider above requirement, I suppose I should go for custom timer job.

However User wants that HR should have a capability to import the data, also while import HR should have a capability to select the appraisal year. If I would consider this requirement I suppose I should go for custom webpart. To fetch the data I should use the oracle data provider.

Could you please provide me your suggestions?

Regards Amit

Answer 1

hi,

you can install Oracle 64bit client on the SharePoint server, and using simple System.Data.OracleClient library to query data from Oracle

Answer 2

Querying data is not a problem I want suggestions on core design. Thanks for ur reply.

Answer 3

Hi Amit,

A custom timer job is appropriate for this type of requirement, because it can be scheduled and monitored. The problem is the requirement to allow individual users to schedule and execute timer jobs from a web part. Because timer jobs can impact the farm you must be a farm administrator to schedule and execute a job. So your code will need to elevate to a farm administrator, however, in most cases the web application application pool account is not the farm administrator. Secondly, having to allow only certain HR users the ability to use the web part will become cumbersome. Finally, you need the ability to store what appraisal year the user selected for the job.

My recommendation would be to create a custom service application. Here the farm administrator can assign certain HR users the permission to use this service application, so they could log into Central Administration and they will only see your service application, they don't need to be farm administrators. Your service application code can now elevate privileges to schedule the job since the application pool account for central administration is the farm administrator. Finally, service applications can have their own database where you could store your data for the scheduled timer job, for example the appraisal year. This database can be very useful for other things such as your own custom auditing. Below is a great link about creating your own service applications.

http://blogs.code-counsel.net/Wouter/Lists/Posts/Post.aspx?ID=149

---

Blog | SharePoint Field Notes Dev Tool | ClassMaster

Answer 4

Hi Amit and Steve,

My 2 cents... To me, creating a custom service application is quite the overkill. You'll need to perform a job repeatedly and you need a UI to give it some config parameters (the appraisal year). I'm kinda inclined to use a site workflow to do this ( http://amavs.wordpress.com/2011/01/12/sharepoint-2010-site-workflow/ , http://www.sharepointbriefing.com/spcode/article.php/3910746/Using-SiteLevel-Workflows-in-SharePoint-2010.htm , http://sharepointresourcecenter.com/sharepoint-2010-workflows-site-workflows.html ), but a web part would also work. Just remember, that if you're importing 1000 rows each time, you can't make your end users wait for it and have to do it asynchronously and provide a status update in the UI for the end users to check how they are doing.



---

Kind regards,
Margriet Bruggeman

Lois & Clark IT Services
web site: http://www.loisandclark.eu
blog: http://www.sharepointdragons.com

Answer 5

Hi Margriet,

Congrats Margriet, finally we have identified application of site workflow. Remember, one day we were discussing about the site workflow application and we were not able to come on conclusion.

 I suppose this scenario is best for site workflow with support of  timer job.  For UI plus scheduling logic we can use the site workflow and for import logic we can use the timer job.

what do you say?

Regards Amit

Answer 6

I do remember this discussion, and it may very well have triggered me to propose it. The site workflow + timer job support sounds very good to me, sounds like a plan!

---

Kind regards,
Margriet Bruggeman

Lois & Clark IT Services
web site: http://www.loisandclark.eu
blog: http://www.sharepointdragons.com