Welcome Guest! Log in
×

Notice

The forum is in read only mode.
Stambia versions 2.x, 3.x, S17, S18, S19 and S20 are reaching End of Support January, 15th, 2024. Please consider upgrading to the supported Semarchy xDI versions. See Global Policy Support and the Semarchy Documentation.

The Stambia User Community is moving to Semarchy! All the applicable resources have already been moved or are currently being moved to their new location. Read more…

Topic-icon Question Error loading hierarchical file containing computed fields into MSSQL

More
25 Jun 2019 18:27 #1 by cbouin
Hello,

I'm trying to load data from a hierarchical file to a MSSQL database.

The file metadata contains a Computed field to define "PK_Sticker", using expression "localPosition()", and a size of 50 ( as described in stambia.org/doc/67-technologies/file/186-using-computed-fields ).

The template is "Action Process LOAD File to Mssql" and tries to load hierarchical file thanks to this SQL order:
BULK INSERT	[StambiaRuntime].[dbo].L1_1g4i7gcugr1wa
FROM 		'\\path_to_the\file.txt'
WITH		(
			FORMATFILE =  '\\path_to_the\file.fmt',
			ROWTERMINATOR = '',
			CODEPAGE = 'ACP',
			FIRSTROW = 1,
			ERRORFILE = '\\path_to_the\file.bad'
		)

The generated fmt file is
9.0
10
1	SQLCHAR	0	50	""	1	L11_PK_Sticker	""
2	SQLCHAR	0	6	""	2	L12_LineType	""
3	SQLCHAR	0	4	""	3	L13_LaunchYear	""
4	SQLCHAR	0	2	""	4	L14_LaunchMonth	""
5	SQLCHAR	0	2	""	5	L15_LaunchDay	""
6	SQLCHAR	0	2	""	6	L16_LaunchHour	""
7	SQLCHAR	0	2	""	7	L17_LaunchMinute	""
8	SQLCHAR	0	2	""	8	L18_LaunchSecond	""
9	SQLCHAR	0	3	""	9	L19_MissionWarehouse	""
10	SQLCHAR	0	10	""	10	L20_DocumentNumber	""

As you can see, the length of the hierarchical file PK is included in the fmt file, making the bulk insert fail because of the 50 skipped chars.
How can I proceed to avoid this behaviour?

Thanks
More
28 Jun 2019 10:58 #2 by Thomas BLETON
If I understand well, you would like to prevent the Computed Field "PK_Sticker" from appearing in the mssql Format File.
Is this correct ?
In this case, I think we can change the Template behavior so that it will not include Computed Fields in the Format File generation.
Can you please open a support ticket and attach an "Export Models and Dependencies" of your mapping to your request ?
Please also mention that you already posted this subject on the forum, so that we can follow up here when we have a solution.