Problem importing 5.5 store download to 7.0.4 store

[mikek]

We are in process of upgrading one of our client sites from AC 5.5 (asp.net) to the latest AC 7.0.4 using the latest DataPort_74 utility.

The 5.5 export process went fine with no errors. We installed fresh AC 7.0.4 with empty DB and where able to import all products, users categories etc. with no issues.

Orders import however returns "data truncation" errors. It seems that some of the data fields in the AC 7.0.4 ac_OrderItems or ac_Orders tables are smaller than the corresponding
fields in the AC 5.5 DB Schema. Have anyone seen this issue before? We have submitted a support case (6104). Mike Randolph from AbleCommerce suggested we post the issue
to the DataPort forum as well.

0% : Initializing import process...
1% : Processing import data...
75% : Importing Order 1 of 38
75% : Importing Order 2 of 38
75% : Importing Order 3 of 38
75% : Importing Order 4 of 38
75% : Importing Order 5 of 38
75% : Importing Order 6 of 38
75% : Importing Order 7 of 38
75% : Importing Order 8 of 38
75% : Importing Order 9 of 38
75% : Importing Order 10 of 38
75% : Importing Order 11 of 38
75% : Importing Order 12 of 38
76% : Importing Order 13 of 38
76% : Importing Order 14 of 38
76% : Importing Order 15 of 38
76% : Importing Order 16 of 38
76% : Importing Order 17 of 38
76% : Importing Order 18 of 38
76% : Importing Order 19 of 38
76% : Importing Order 20 of 38
76% : Importing Order 21 of 38
76% : Importing Order 22 of 38
76% : Importing Order 23 of 38
76% : Importing Order 24 of 38
76% : Importing Order 25 of 38
77% : Importing Order 26 of 38
77% : Importing Order 27 of 38
77% : Importing Order 28 of 38
77% : Importing Order 29 of 38
77% : Importing Order 30 of 38
77% : Importing Order 31 of 38
77% : Importing Order 32 of 38
77% : Importing Order 33 of 38
77% : Importing Order 34 of 38
77% : Importing Order 35 of 38
77% : Importing Order 36 of 38
77% : Importing Order 37 of 38
78% : Importing Order 38 of 38
78% : Orders Import Complete...
100% : Import Completed Successfully!

------------------ ERRORS REGISTERED DURING IMPORT ------------------
CHUNK # 18
------------------------------------

Error: Order Item Import, OrerItemID:52963 , OrderID: 27559 , OrderNumber: 563, String or binary
data would be truncated.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean
breakConnection)
a(for more details check server log)...

Error: Order Item Import, OrerItemID:41842 , OrderID: 27643 , OrderNumber: 28, String or binary
data would be truncated.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean
breakConnection)
at(for more details check server log)...

Error: Order Item Import, OrerItemID:42248 , OrderID: 27666 , OrderNumber: 35, String or binary
data would be truncated.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean
breakConnection)
at(for more details check server log)...

Error: Order Item Import, OrerItemID:69693 , OrderID: 27680 , OrderNumber: 1704, String or binary
data would be truncated.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean
breakConnection)
(for more details check server log)...

Error: Order Item Import, OrerItemID:41882 , OrderID: 27884 , OrderNumber: 31, String or binary
data would be truncated.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean
breakConnection)
at(for more details check server log)...

Error: Order Item Import, OrerItemID:42028 , OrderID: 28017 , OrderNumber: 33, String or binary
data would be truncated.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean
breakConnection)
at(for more details check server log)...

Error: Order Item Import, OrerItemID:42027 , OrderID: 28019 , OrderNumber: 32, String or binary
data would be truncated.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean
breakConnection)
at(for more details check server log)...

All orders have negative amount after the import:

For instance:

Payment:
Amount: $199.50
Status: Completed
Ref: Visa - x5654
AVS: n/a
CVV:

[Naveed]

Orders import however returns "data truncation" errors. It seems that some of the data fields in the AC 7.0.4 ac_OrderItems or ac_Orders tables are smaller than the corresponding

When you connect Universal DataPort to your store it tries to detect if any field length needs to be adjusted in database. And it asks for a permission to correct those.. Are you getting this issue even after updates/correction made by DataPort?

[mikek]

Hi Naveed,

Yes, during the initial import the DataPort detects and automatically modifies few table fields, but those data fields are related to the following tables:

Field length should be extended from '100' to '255' for field 'Name' for database table 'ac_DigitalGoods'.
Field length should be extended from '100' to '255' for field 'FileName' for database table 'ac_DigitalGoods'.
Field length should be extended from '100' to '255' for field 'MediaKey' for database table 'ac_DigitalGoods'.
Field length should be extended from '100' to '255' for field 'Name' for database table 'ac_GiftCertificates'.
Field length should be extended from '40' to '50' for field 'SerialNumber' for database table 'ac_GiftCertificates'.
Field length should be extended from '100' to '255' for field 'Name' for database table 'ac_OptionChoices'.
Field length should be extended from '20' to '255' for field 'SkuModifier' for database table 'ac_OptionChoices'.
Field length should be extended from '100' to '255' for field 'Name' for database table 'ac_OrderItemDigitalGoods'.

The error seems the be related to ac_OrderItems and ac_Orders tables not listed above.

[Naveed]

Hello Mike,

The only workaround I can suggest is to manually review the AC55 exported xml data for those Order items you are receiving an error message. Look for any string type field whose length is causing the issue and fix that field length before the import.

Error: Order Item Import, OrerItemID:42027 , OrderID: 28019 , OrderNumber: 32, String or binary
data would be truncated.

For example, for the above error message, look for OrderItem with ID = 42027, in XML data, and try to find the field whose data exceeding maximum length.

[Logan Rhodehamel]

Agreed with Naveed. If you can find your export data and search for this:

<OrderItem ID="52963"

Then post the whole <OrderItem> block here... assuming it does not contain anything confidential. That would help us track down what we might be facing and fix the dataport. Also we should be able to suggest a manual database adjustment that would allow you to continue the import.