0

Recently i'm importing data from CRM dynamics 11 to our sql database on daily basis. I have created an SSIS package which does the import. My issue is in CRM i have member's date of birth as "1990-04-25" but when i import it to our sql database, it comes as "1990-04-24 23:00:00.000". The issue is it deduct one day back from the date of birth. The issue is only with those date of birth which has date time attached to it.

Any solution to the above issue please?

NOTE :- my table data type is - datetime , and same goes for the CRM.

date flow i'm using in ssis is an add on from kingswaysoft.

Kind Regards...

Solution :- Solution can be found here https://social.msdn.microsoft.com/Forums/sqlserver/en-US/67564e18-ea49-4d90-bc79-18e3213cffb2/loading-datetime-field-gives-wrong-dates-in-ssis?forum=sqlintegrationservices . It has been fixed.

According to the description, the issue is that BisStartDate column displays one hour before the actual datetime in SSIS.

To work around this issue, we can use a Derived Column that contains the correct datetime to replace the BisStartDate column, then use the new BisStartDate column to do the following transformations. The following screenshot is for your reference:

Biswa
  • 331
  • 6
  • 22
  • That may worth adding that solution as an answer here. Adding the link as well as any relevant text to guide future searchers here on SO. Noting that it is a TimeZone issue will be helpful if someone gets stuck on this in the future. – JNevill Jul 30 '18 at 14:17
  • In our CRM source component, there is an "Output Timezone" option which you can choose. It could be one of the options to get the right datetime output instead of using any conversions. – Daniel Cai Jul 30 '18 at 19:56
  • added the screenshot and solution – Biswa Jul 31 '18 at 12:32
  • You are allowed to self answer, so edit the question, take out the solution & write it in place for answer including screenshot, content from the link + link. – Arun Vinoth-Precog Tech - MVP Jul 31 '18 at 22:05

0 Answers0