question

gaupop-4423 avatar image
0 Votes"
gaupop-4423 asked FahdRehman-4234 commented

Sourcing DT_DBTIMESTAMP field from Odata 4 url to SSIS

Hi,

I am facing an issue while trying to source an OData 4 url (to fetch CRM Nav data)from SSIS package. The source has date fields of type DT_DBTIMESTAMP, when it comes to SSIS target, it populates as null. I even tried to add a data conversion operator before getting to the target, but still getting null.
Any solution for such issue? anyone faced this earlier? Thanks a bunch!

sql-server-integration-services
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi, did you get to find a solution or work around to this? Any help is much appreciated.

Regards,
Fahd Rehman.

0 Votes 0 ·

@FahdRehman-4234 -This had to be resolved from the source system end. We raised a request with them to change the data type of impacted fields from Date format to DateTime. They could change it and it worked seamless in SSIS. I could not find a way to handle this in SSIS.

1 Vote 1 ·

Thank you for quick response, appreciate it :)

0 Votes 0 ·

1 Answer

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered gaupop-4423 commented

Hi @gaupop-4423,

In the OData Source, have you tried to preview the data to ensure that there is correct date there?

I tested locally that I couldn't reproduce the issue. Make sure that the datatype is DT_DBTIMESTAMP.

115660-screenshot-2021-07-19-162009.jpg

If possible, could you please show us the dataflow designer?

You may also raise the issue to the CRM-forum for more suggestions,
https://community.dynamics.com/crm/f/microsoft-dynamics-crm-forum

Regards,

Zoe


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October



· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Zoehui-MSFT ,
Thanks for your inputs. I face below error while trying to preview data, but when I execute the flow, it loads all other fields except Date fields.

Would you have any insight on this?
~

TITLE: System.Data



Unable to cast object of type 'Microsoft.OData.Edm.Library.Date' to type 'System.IConvertible'.Couldn't store <2020-09-08> in DateQuoteCreated Column. Expected type is DateTime.


ADDITIONAL INFORMATION:

Unable to cast object of type 'Microsoft.OData.Edm.Library.Date' to type 'System.IConvertible'. (System.Data)


BUTTONS:

OK


0 Votes 0 ·

Hi @gaupop-4423, it seems that the issue is related with your CRM datasource, please refer this blog to see if it it helpful.


0 Votes 0 ·

Thanks @Zoehui-MSFT - I will check if this helps. I think I will have to do some scripting and try to convert incoming date to an acceptable format in SSIS. Please let me know if you still find any other solution, would greatly appreciate.

0 Votes 0 ·
Show more comments