Recently we had a requirement to write an SSIS package that will fetch the data from SQL Server and will insert it (or create records) into Dynamics 365 CE.

For connecting and fetching data from SQL Server, we were using OLE DB Source Component with data access mode as SQL Command which had one parameter, whose value we had to fetch from Dynamics 365 CE.

For passing the value to this parameter we had defined a variable within our Package named LastUpdateFromCRM.

Now as we wanted to set the variable value from one of the records in Dynamics 365 CE, we used KingswaySoft’s CDS/CRM Source Component Editor, that will return us one row using fetch xml + max rows returned property.

Then we added a script component to the output of the CDS/CRM Source Component.

Here double-clicking the Script Component and selecting Input Columns inside the editor, we can select the input columns that we want to use within the Script Editor.

Next, we selected Script section inside the editor, there we selected the user variable as Read Write variable (as we are setting its value inside our script) and clicked on Edit Script button to open the VSTA Project.

Within VSTA project, inside main.cs we defined a date time variable to hold value coming from the CRM Source Component i.e. the value of last update field.

Inside ProcessInputRow method, we then set the value of that variable and finally within Post Execute method we use it to set the value of our user-defined variable.

Now as we have specified the value to our LastUpdateFromCRM variable, its value was passed to the SQL Command of OLE DB Source component as intended.

Hope it helps..

In Dynamics 365 CE, the data time field’s value will always be returned in UTC time when retrieved through the Organization Service.

Just go through this insightful article to learn more about CRM Date Time.

https://www.powerobjects.com/2012/06/07/crm-2011-truths-about-datetime/

For e.g.

Within application à

Inside CRM

Now to get it in Local Time of the User we can use LocalTimeFromUTCTimeRequest class.

https://www.inogic.com/blog/2013/06/how-to-convert-the-datetime-in-local-and-utc-date-time-format/

Now the good thing is if we are using CDS/CRM Source Component of KingswaySoft’s SSIS Integration Toolkit, it has the Output Timezone property.

It has following 3 values.

Here UTC (Default) will return the value in UTC and Adjust to the time zone of connection user or the impersonation user will return values based on the user’s time zone. This property makes it extremely intuitive and easy to work with DateTime field within Dynamics 365 CE.

Hope it helps..