I am trying to add a numeric value within the description field on each Cost Center we have imported previously.
However, I am getting the following error message when I execute the Simulate Action:
"Explicit conversion from data type int to ntext is not allowed."
Has anyone else seen this issue?
Apr 08, 2020 08:50 AM
Apr 08, 2020 12:25 PM
If you can share the adapter as @Nico_Erasmus has suggested along with a debug log showing the import behavior and failure then that will likely allow a more specific response. However based on the error message I am guessing you will need to arrange for the source data query to bring in the description value as a text value rather than a numeric value - that can probably be arranged with some sort of type cast.
Apr 08, 2020 07:35 PM
Please share your xml (what you can) and sample data.
"See attached .csv source file and .xml business adapter file."
When you say add, do you mean concatenate, so you are effectively editing the previous CC value or are you adding a new level to your structure?
"I want to update the Description row of the Cost Centers based on the value that is listed in my .csv file."
Apr 08, 2020 08:40 PM
Try using a query like the following to get the source data from the CSV file - this will cast the Description values to text so they can be safely imported:
SELECT Name, [Parent Path], CSTR(Description) AS Description FROM [ArcherApplicationMappingTemplate.csv]
Alternatively, place an appropriately formed schema.ini file in the same directory as your .csv file to configure the data type of each column to be text.
Without either of these approaches, the Microsoft ODBC driver that reads CSV files will see the numbers you have in the "Description" column and treat the data from there as numeric - but you need it to be treated as text data to be imported into the Description property of cost centers.
Apr 08, 2020 08:49 PM
you get this error message when you try to convert a ntext field directly into a integer field.
DECLARE @MyTable TABLE ([Description] ntext )
INSERT INTO @MyTable
This table has one column 'Description' which has the data type = ntext and the value '123456789'
SELECT CONVERT(INT, [Description] ) from @MyTable
will return exactly that message. If you convert first into VARCHAR and then to int, that will work.
SELECT CONVERT(INT, CONVERT(VARCHAR(50),[Description])) from @MyTable
So if you are using a database table as input data for BI, you need to convert this ntext field first into a varchar field. When the content of this column has only digits, then BI will be able to convert this content implicitly into an int and insert into the column which is defined as int.
Apr 08, 2020 02:25 PM
If you're working with Excel (not CSV) files please make sure to format all cells as Text cells in Excel.
For CSV files I think that the recommendations already provided above in this thread provides the guidance needed...
However, please don't hesitate to share a sample of both the adapter configuration and a sample file with data if you need further guidance.
Oct 12, 2021 09:41 AM