SSIS Dynamics CRM Source with dynamic FetchXML errors

[Jonathan Mills]

Hi all

I'm using fetchXML from a table and looping through a series of fetchXML queries and retrieving the data from them (and subsequently loading them into a OLE DB table) using the "THUNK_COLUMN" method from your video.

All is good in the world on *some* of my queries - however on others I am getting a "[Dynamics CRM [1]] Error: An error occurred with the following error message: "Failed to process column 'mmis_locationassetidname'. The value is too large to fit in the column data area of the buffer."." exception. 

Thoughts

#1 - the field isn't big enough in my External & Output columns - but of course this is a dynamic query so only THUNK_COLUMN is there (i've made it a DT_TEXT).

#2 - this actual field (mmis_locationassetidname) is the name of a linked entity, so this data is coming across in an XML attribute of the mmis_locationassetid element. Is this significant? Does the Cozyroc CRM source lookup the length of these attribute based fields when creating the runtime output columns? the maximum length of  is mmis_locationassetidname 116 chars in my case

#3 - this field isn't even a column on my table, so it'll just get dropped out anyway. Grrrrr. I can't "not select it" either, it comes automatically when I request the  mmis_locationassetid attribute

fetchXML attached...

What can be done to cure this?

Help!

Jon

[Ivan Peev]

Hi Jon,

Have you tried configuring the Dynamics CRM Source to see what column type and length will be set for mmis_locationassetidname column ? I suspect the length will not be enough to accommodate the complete column text. The column metadata is provided back by the Dynamics CRM service.

[Jonathan Mills]

When I fetch it using the Kingswaysoft source, it registers as nvarchar 255

[Ivan Peev]

Jon,

What about COZYROC ?

[Jonathan Mills]

Rather less usefully, COZYROC has decided its only 100 characters long.....

[Ivan Peev]

Jon,

If you are seeing 100 characters long column, this is how much it is reported back by the Dynamics CRM service. You can use the Fiddler application to see for yourself what is being reported back by the service.

[cesar...@gmail.com]

Hi,

It looks like sometimes the crm server it returns a bad length variable to ADO ssis service.In that case, if you want to use THUNK_COLUMN, you have to take into account you should avoid to load that column to load the another ones dinamically. So previously you have to avoid to use that column in destination to load all columns except that one.

In other case, if you need to load that column you can't use THUNK_COLUMN, You shold a normal load for that entity, and force to change his metadata manually to increase his length (through Show Advanced Editor)

GL