Thursday, March 4, 2010

Exporting EmployeeStartDate to FIM 2010 Portal

Little documented is the date format of the EmployeeStartDate and the EmployeeEndDate in Forefront Identity Manager 2010.

If you look into the date format of the attributes of a user in the portal it self, it'll tell you that the format is M/d/yyyy hh:mm tt. Feeding the FIM MA this format will make it "sick".

The correct format would be to se the builtin function DateTimeFormat in the Inbound Synchronization Rule and use the function DateTimeFormat, so that the end result would be:

DateTimeFormat(HireDate,"yyyy-MM-ddTHH:mm:ss.000")

Here is a screenshot with an Inbound Flow Rule from a live installation doing just that (or close to, as I want a specific time stamp to go into the portal, but you should get the idea) -


This will convert a SQL DateTime attribute (HireDate) to the format the FIM portal wants it in.

10 comments:

JWillems said...

Thanks so much!
I was working on this the whole day,
when exporting from my FIM MA, i got all empty information in my portal cause of this.

I have still one small problem though
the date is in my SQL database as a varchar and contains "11/11/2010"
in the FIM portal it looks like this "11/10/2010 4:00:00 PM"

any idea why FIM puts back the day one day earlier?

Søren Granfeldt said...

Hi, JWillems

Your problem must likely have to do with the fact that you're missing the time part of your date and therefore it'll see the time as being Midnight, which may result in SQL setting the default timestamp of 4:00 PM. Now, I havn't tested this, so this is only a guess. Try opening a SQL table on the FIM server with a DateTime column and type the date mentioned and see what you get - if I'm right, you may have to tweak your your SQL settings - or even better get your data straight in the source if at all possible. The last one has always been my favourite :-)

JWillems said...

Thanks for your prompt reply,

Your favorate sollution is also mine, I changed the query to my SQL table to the current time will be added to the startdate field.

The problem is solved now.

DonalC said...

I'm new to FIM and I'm hoping you can help me. I'm having the same issue and am not clear how I can apply the DateTimeFormat solution you mentioned. Is this done in an Advanced attribute flow using a coded extension? Or is there a way to do this codeless?

Søren Granfeldt said...

@DonalC: You could do either; if you're already doing SR, you could apply the DateTimeFormat to the Import flow rule from the system providing your hire date. Or you could do this in a coded extension for the same MA.

DonalC said...

Thank you for getting back to me so quickly. I'd be very keen to use a codeless solution but I'm a little unclear on how to apply the DateTimeFormat to the Import flow rule. The only place I can think of to put the transform is in the 'Constant' field of and Advanced Flow option, however this doesn't seem right.
Any guidance you could give would be greatly appreciated.

Søren Granfeldt said...

I'd suggest that you take a look at this; http://technet.microsoft.com/en-us/library/ee534911(WS.10).aspx

DonalC said...

Soren, I'm sorry to bug you again. I've read through the full getting started guide but am still at a loss how to modify the date format using the inbound SR.

The inbound SQL SR is taking the data in without problems, however because of the date format it won't export to the portal.

I see from the guide you referenced that I can concatenate values while running the syncronisation. However I can't find any referece to where I might modify the date format while running the sync.

I'm sure I'm being really stupid and missing something very obvious but would really appreaciate any guidance you could give.

Thank you for your patience as I'm sure this must be trying.

Søren Granfeldt said...

@DonalC: You should be able to do this using the built-in functions in the Syncrhonization Rules. I'll update the blog post with a screenshot.

Alex Batrachenko said...

Hello!
I'm trying to make emploeeStartDate flow from DB to FIM Portal.
I have tryed your solution but it is not works for me.

I have created a such flow only in Sync Rules on Portal (without attribute flow in MA).
Maybe you can help me with debugging?
Thanks!