Trying to map invalid data type sql to dynamics 365

  • 14Views
  • Last Post 04 April 2019
0
votes
Andy Le posted this 03 April 2019

1. If I pull data from sql and exported to excel, upload it to skyvia and do upsert to dynamics 365, works fine.

If I try to do it straight from sql to skyvia is where it fails.  It looks to be invalid data type.

When I'm doing upsert, if record doesn't existing in dynamics, it create a new rcord, if there is, it'll update with new information.

-Dynamics have a accountid that is unique to each row that is generate by dynamics. So I set customerID as a lookup.

I'm not able to do this with targetlookup since it's invalid data.

What are my options? I started testing some expression, but didn't work.

 

Attachment is show below if I upload via excel vs sql directly.

Attached Files

Order By: Standard | Newest | Votes
0
votes
Mariia Zaharova posted this 04 April 2019

Can you please specify what exactly scenario you would like to implement? If you are interested in the Upsert operation, you need to use Target Lookup mapping with the "Set null when no match found" option. To use the Lookup mapping, you need to have a column (or a set of columns) that uniquely identifies a record, other than the primary key. Whether the "CustomerCode" column in your database table contains values that correspond to the Account Id values? Account Id values look like this:

 

So, if your "CustomerCode" column contain another values, your import will fail. 

Your lookup may look like this:

 

This lookup will try to find the record in account entity (Dynamics CRM) where name = CustomerName (database column). If no record is found, insert will be performed, if record is found update will be perfomed. This an example, you need to adjust it to your scenario (find set of source and target columns that will uniquely one record.

 

Please tell me if this helps.

 

0
votes
Andy Le posted this 04 April 2019

Unfortunately, our source column is customer code.

That is the invalid data type so I have to use expressions.

Close