How to parse MongoDB Date time in SSIS

Introduction

In our previous post we explained how to read/load MongoDB data in SSIS. This post covers specifically how to parse MongoDB date time stored inside your MongoDB documents. By default any well known date formats (e.g. ISO date) will be parsed as valid datetime (e.g. DT_DBTIMESTAMP) when you use SSIS MongoDB Source. But if you have date stored as other format (e.g. MM-dd-yyyy) then system will not parse as string unless you specify custom date format on JSON Datetime tab. If you want to learn How to query Date in MongoDB then read this article

Parse MongoDB Date time using SSIS MongoDB Source

Perform the following steps to enable custom date format parsing for date not stored as ISODate in MongoDB. Below steps assume you have Downloaded and Installed SSIS PowerPack from Here

NOTE: Below steps not necessary if your date is stored as ISODate in MongoDB…. e.g.  { “OrderDate” : ISODate(“2015-12-31T00:00:00Z”)  }

1. Open MongoDB Source. Change Mode to Query Mode
2. Check Enable JSON option. You don’t have to type filter (its optional)
3. Goto JSON Options and then Datetime options Tab
4. Enter custom date format (The way your date stored in MongoDB)

Now go to columns and click refresh columns. It will detect column as DT_DBTIMESTAMP (e.g. date/time)

SSIS MongoDB Source- Date Time Options ( Parse MongoDB Date)

SSIS MongoDB Source – Query and Parse MongoDB Date with Custom format