Date Format not correct - Business Importer - FlexNet Manager on Prem

[romanmensch]

Hello everyone,

we have current the problem that our Business Importer is importing the dates incorrectly into the database. Although the XML is correct. Has anyone encountered this mistake? Is this because of the version of Business Importer? 

FlexnetManager 2020 R1

Business Importer Version: 15.0.0.4

I am very grateful to you for helpful answers.

[mfranz]

Hi Roman,

Dates can be tricky because local system settings are usually used when interpreting the format. Do you have any details on the imported file and system language settings?

Best regards,

Markward

[romanmensch]

Hi Markward,

thanks a lot for fast Feedback.

We have installed the Business Importer on the application server. And the DB is on a different server.

Here are the details in the Pictures from the Application Server, where we make the Import. 

And the XML also in the Pictures.

Which settings really need to be set?

And which would be the correct regional settings?

Best Regards,
Roman

[mfranz]

Hi Roman,

Your server settings have day-month-year, while your XML file has year-month-date. I guess that could be an issue. Maybe you try US settings? Be aware that changing this might have an impact on other existing imports.

We usually run a 2 stage approach:

1. Extratcting the data without any interpretation
2. Transforming it, using proper SQL in a second import step (having the business import load the data into FNMS)

With these individual transformations we can import form different sources, using different date formats.

Best regards,

Markward

[romanmensch]

Hi Markward

Yes, we also tried EN US but that is probably also in the wrong format. 

Where do you put a "proper SQL" in the Business Importer? See how we did it in the attached picture.

Best Regards,

Roman

[mfranz]

Hi Roman,

Here's how I usually do all business imports:

Have an import step just transfering the data, without transforming it (1). Please check out the "Advanced Properties" "Use Physical Table" option. This ensures the data persists after the first import step ended. You might have to enable this area in the BAS options first. Then have the actual import step (2) access this data using SQL (3).

Now here you can use all the SQL magic to transform your data. It looks like you might want to use CONVERT. Your date format looks like ISO6801.

https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15 

Best regards,

Markward

[romanmensch]

Hi Markward

Thank you for your great tip. We did that but can't find the point how we can write the data back?

Here the wrong Data:

Than we make the physical Table and now the Data is correct:

And now we stand in line. We cannot write the data back.

An error has occurred when trying to run the query [SELECT *

FROM ECMImport_Import1_DATA]. The error message is [Column names in each table must be unique. Column name 'ROWNUMBER' in table '#ECMImport_Import3' is specified more than once.].

Column names in each table must be unique. Column name 'ROWNUMBER' in table '#ECMImport_Import3' is specified more than once.

Column names in each table must be unique. Column name 'ROWNUMBER' in table '#ECMImport_Import3' is specified more than once.

How do we get the staging table that references the import table?

We are already very close, maybe you have another great tip for us.

Best Regards,
Roman

[mfranz · Accepted solution]

Hi Roman, 

Business Imports will always add their own columns, like ROWNUMBER or matched. Therefore you cannot use a query returning the same column names. You'll have to try something like:

SELECT
	[ContractNo]
	,CONVERT(DATE, [PropertyValue], 126) AS Date
FROM ECMImport_Import1_DATA

 Somewhere in your export process, there is a Byte Order Mark (BOM) created. That's those weird looking characters in from of your ContractNo. IF you can get rid of it, it may make things easier.

Best regards,

Markward

[romanmensch]

Dear Markward,

We were able to solve it using the physical table. We have now solved it with a workaround. The correct data from the DB (physical Table) is copied into Excel and imported again. Not so nice but it worked for us. Many many thanks.