romanmensch
Active participant

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

Jump to solution

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.

 

0 Kudos
1 Solution

Accepted Solutions
mfranz
Shining star

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

Jump to 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

Softline Group is Europe's leading independent expert in Software Asset Management.

View solution in original post

8 Replies
mfranz
Shining star

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

Jump to solution

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

Softline Group is Europe's leading independent expert in Software Asset Management.
0 Kudos
romanmensch
Active participant

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

Jump to solution

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

0 Kudos
mfranz
Shining star

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

Jump to solution

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

Softline Group is Europe's leading independent expert in Software Asset Management.
0 Kudos
romanmensch
Active participant

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

Jump to solution

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

0 Kudos