{"id":946,"date":"2017-01-18T14:57:50","date_gmt":"2017-01-18T14:57:50","guid":{"rendered":"http:\/\/zappysys.com\/blog\/?p=946"},"modified":"2018-11-14T13:55:10","modified_gmt":"2018-11-14T13:55:10","slug":"parse-mongodb-date-time-in-ssis-custom-format","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/parse-mongodb-date-time-in-ssis-custom-format\/","title":{"rendered":"How to parse MongoDB Date time in SSIS"},"content":{"rendered":"

Introduction<\/h2>\n

\"\"In our previous\u00a0post we explained how to read\/load MongoDB data in SSIS<\/a>. This post covers specifically how to parse MongoDB date time<\/strong> 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<\/a>. But if you have date stored as\u00a0other format (e.g. MM-dd-yyyy) then system will not parse as string unless you\u00a0specify custom date format on JSON Datetime tab. If you want to learn How to query Date in MongoDB then read this article<\/a><\/p>\n

Parse MongoDB Date time using SSIS MongoDB Source<\/h2>\n

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<\/a><\/p>\n

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

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

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

     <\/p>\n

    \"SSIS<\/a>

    SSIS MongoDB Source- Date Time Options ( Parse MongoDB Date)<\/p><\/div>\n

    \"SSIS<\/a>

    SSIS MongoDB Source – Query and Parse MongoDB Date with Custom format<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"

    Introduction In our previous\u00a0post 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 […]<\/p>\n","protected":false},"author":1,"featured_media":2115,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[45],"tags":[139,6,46,12,89,4],"yoast_head":"\r\nHow to parse MongoDB Date time in SSIS | ZappySys Blog<\/title>\r\n<meta name=\"description\" content=\"Learn how to parse MongoDB date time which is custom format. Use SSIS MongoDB Source Connector to read data from collection and parse custom date format.\" \/>\r\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\r\n<link rel=\"canonical\" href=\"https:\/\/zappysys.com\/blog\/parse-mongodb-date-time-in-ssis-custom-format\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"How to parse MongoDB Date time in SSIS | ZappySys Blog\" \/>\r\n<meta property=\"og:description\" content=\"Learn how to parse MongoDB date time which is custom format. Use SSIS MongoDB Source Connector to read data from collection and parse custom date format.\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/zappysys.com\/blog\/parse-mongodb-date-time-in-ssis-custom-format\/\" \/>\r\n<meta property=\"og:site_name\" content=\"ZappySys Blog\" \/>\r\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/ZappySys\/\" \/>\r\n<meta property=\"article:published_time\" content=\"2017-01-18T14:57:50+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2018-11-14T13:55:10+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/08\/mongodb-logo.png\" \/>\r\n\t<meta property=\"og:image:width\" content=\"88\" \/>\r\n\t<meta property=\"og:image:height\" content=\"88\" \/>\r\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\r\n<meta name=\"author\" content=\"ZappySys\" \/>\r\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\r\n<meta name=\"twitter:creator\" content=\"@https:\/\/twitter.com\/zappysys\/\" \/>\r\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"ZappySys\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/zappysys.com\/blog\/parse-mongodb-date-time-in-ssis-custom-format\/\",\"url\":\"https:\/\/zappysys.com\/blog\/parse-mongodb-date-time-in-ssis-custom-format\/\",\"name\":\"How to parse MongoDB Date time in SSIS | ZappySys Blog\",\"isPartOf\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/zappysys.com\/blog\/parse-mongodb-date-time-in-ssis-custom-format\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/zappysys.com\/blog\/parse-mongodb-date-time-in-ssis-custom-format\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/08\/mongodb-logo.png\",\"datePublished\":\"2017-01-18T14:57:50+00:00\",\"dateModified\":\"2018-11-14T13:55:10+00:00\",\"author\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\"},\"description\":\"Learn how to parse MongoDB date time which is custom format. Use SSIS MongoDB Source Connector to read data from collection and parse custom date format.\",\"breadcrumb\":{\"@id\":\"https:\/\/zappysys.com\/blog\/parse-mongodb-date-time-in-ssis-custom-format\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/zappysys.com\/blog\/parse-mongodb-date-time-in-ssis-custom-format\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/parse-mongodb-date-time-in-ssis-custom-format\/#primaryimage\",\"url\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/08\/mongodb-logo.png\",\"contentUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/08\/mongodb-logo.png\",\"width\":88,\"height\":88},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/zappysys.com\/blog\/parse-mongodb-date-time-in-ssis-custom-format\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/zappysys.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to parse MongoDB Date time in SSIS\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/zappysys.com\/blog\/#website\",\"url\":\"https:\/\/zappysys.com\/blog\/\",\"name\":\"ZappySys Blog\",\"description\":\"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/zappysys.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\",\"name\":\"ZappySys\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g\",\"caption\":\"ZappySys\"},\"sameAs\":[\"http:\/\/www.zappysys.com\/\",\"https:\/\/www.facebook.com\/ZappySys\/\",\"https:\/\/twitter.com\/https:\/\/twitter.com\/zappysys\/\"],\"url\":\"https:\/\/zappysys.com\/blog\/author\/admin\/\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to parse MongoDB Date time in SSIS | ZappySys Blog","description":"Learn how to parse MongoDB date time which is custom format. Use SSIS MongoDB Source Connector to read data from collection and parse custom date format.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/zappysys.com\/blog\/parse-mongodb-date-time-in-ssis-custom-format\/","og_locale":"en_US","og_type":"article","og_title":"How to parse MongoDB Date time in SSIS | ZappySys Blog","og_description":"Learn how to parse MongoDB date time which is custom format. Use SSIS MongoDB Source Connector to read data from collection and parse custom date format.","og_url":"https:\/\/zappysys.com\/blog\/parse-mongodb-date-time-in-ssis-custom-format\/","og_site_name":"ZappySys Blog","article_author":"https:\/\/www.facebook.com\/ZappySys\/","article_published_time":"2017-01-18T14:57:50+00:00","article_modified_time":"2018-11-14T13:55:10+00:00","og_image":[{"width":88,"height":88,"url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/08\/mongodb-logo.png","type":"image\/png"}],"author":"ZappySys","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/twitter.com\/zappysys\/","twitter_misc":{"Written by":"ZappySys","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/zappysys.com\/blog\/parse-mongodb-date-time-in-ssis-custom-format\/","url":"https:\/\/zappysys.com\/blog\/parse-mongodb-date-time-in-ssis-custom-format\/","name":"How to parse MongoDB Date time in SSIS | ZappySys Blog","isPartOf":{"@id":"https:\/\/zappysys.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/zappysys.com\/blog\/parse-mongodb-date-time-in-ssis-custom-format\/#primaryimage"},"image":{"@id":"https:\/\/zappysys.com\/blog\/parse-mongodb-date-time-in-ssis-custom-format\/#primaryimage"},"thumbnailUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/08\/mongodb-logo.png","datePublished":"2017-01-18T14:57:50+00:00","dateModified":"2018-11-14T13:55:10+00:00","author":{"@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82"},"description":"Learn how to parse MongoDB date time which is custom format. Use SSIS MongoDB Source Connector to read data from collection and parse custom date format.","breadcrumb":{"@id":"https:\/\/zappysys.com\/blog\/parse-mongodb-date-time-in-ssis-custom-format\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/zappysys.com\/blog\/parse-mongodb-date-time-in-ssis-custom-format\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/parse-mongodb-date-time-in-ssis-custom-format\/#primaryimage","url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/08\/mongodb-logo.png","contentUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/08\/mongodb-logo.png","width":88,"height":88},{"@type":"BreadcrumbList","@id":"https:\/\/zappysys.com\/blog\/parse-mongodb-date-time-in-ssis-custom-format\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/zappysys.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How to parse MongoDB Date time in SSIS"}]},{"@type":"WebSite","@id":"https:\/\/zappysys.com\/blog\/#website","url":"https:\/\/zappysys.com\/blog\/","name":"ZappySys Blog","description":"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/zappysys.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82","name":"ZappySys","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g","caption":"ZappySys"},"sameAs":["http:\/\/www.zappysys.com\/","https:\/\/www.facebook.com\/ZappySys\/","https:\/\/twitter.com\/https:\/\/twitter.com\/zappysys\/"],"url":"https:\/\/zappysys.com\/blog\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/946"}],"collection":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/comments?post=946"}],"version-history":[{"count":5,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/946\/revisions"}],"predecessor-version":[{"id":5368,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/946\/revisions\/5368"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media\/2115"}],"wp:attachment":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media?parent=946"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/categories?post=946"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/tags?post=946"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}