Skip to main content
To homepage To homepage

We are just started to use ODX server as a source for our countries that are migrated to Business Central in Azure. On our DEV environment i want to limit the data to only get entries after a certain date. Normally i would use a data selection rule on Posting Date Greater or Equal 2021-01-01

When i want to do that on ODX server as a data source i get:

Conversion failed when converting date and/or time from character string.

I have tried every date notation i can think of but i always get this same error.

Anyone any idea on how to filter dates from the ODX Server?

I tested some more by adding some mapping and it still works.

Now why does this not work on the other table. 

I tried mapping the ODX Server again and it still won't work. Can it be some bug in TX?

@RobertU can you please clarify what you mean by extension regarding “The DWH_GLEntry is part of an extension that has been created for us”? Also, can you please try adding PKs and incremental load to the new working table you created? If this works, please try to apply changes one by one to make the new working table the same as the table with the error

 

 

I will try to recreate another table that has the same problem but is much smaller. GLEntry is way too big to just recreate it. That would mean a lot of work.

What i mean by extension is that our BC developer has created an extension for BC that they can import to a company that uses BC to create all the web extensions that we need to get the data.

You may be able to get a hint of what is going wrong by looking at the SSIS package syntax generated for an example with a single mapping vs. multiple mappings: right-click the table > Advanced > Customize Code > SSIS Package > Standard editor > Default Package. This will give you the content of a dtsx file that would be run in SSIS to do the data movement.

The difference between the filter applied to one mapping vs, multiple might show you what is going on syntax-wise. If you were not able to run the filter on a single-mapping I would suggest checking the source collation in case it has a different date literal format.

I have tested some more. I have created 2 tables: Accounting Period Test and Accounting Period Test 2.

The 2 tables are setup exactly the same. The only difference is that in Accounting Period Test i started with the datasource coming from BC. That one works and keep working.

The other table i started with a datasource coming from a NAV2017 datasource from ODX business unit and that one does not work.

Recreating all tables is not an option, so is this a bug in TX?

 

 

Removing all the other sources and leaving only the ODX(IT) source does not fix the problem

Hi @RobertU 

It would probably be best if we took a look at it together. I can’t replicate the issue with my setups.

We can discuss when it fits and similar with private messages.

Reply


Terms and ConditionsCookie settings