We're migrating from GP to BC and receiving the following error message. I've reveiwed IV10001 and IV30301 and there
are no transactions with 0 or Null in TRXQTY.
If I run Diagnostic I receive notice 47 Tables Successful, including GP Item Transactions.
How many tables should I expect in the Tables Successful box?
TEST0$GP Item Transactions$feeb3504-556e-4790-b28d-a2b9ce302d81 |
Failure happened on 'Sink' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed. Please search error to get more details.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.InvalidOperationException,Message=Column 'Quantity' does not allow DBNull.Value.,Source=System.Data,' |
Hi SMS,
I suggest you run the following script against the GP company database you’re migrating from. This script will show you inventory quantities being extracted from GP to be pushed into BC:
SELECT CONVERT(NVARCHAR(75), (substring(a.ITEMNMBR,1,20))) AS [No], CONVERT(NVARCHAR(11), rtrim(a.TRXLOCTN)) AS [Location], CONVERT(DATETIME, a.DATERECD) AS [DateReceived], (CASE ITMTRKOP WHEN 2 THEN 1 WHEN 3 THEN b.QTYRECVD-b.QTYSOLD ELSE a.QTYRECVD-a.QTYSOLD END) AS [Quantity], CONVERT(NVARCHAR(21), RCPTNMBR) AS [ReceiptNumber], COALESCE(CONVERT(NVARCHAR(21), rtrim(SERLNMBR)), '') AS [SerialNumber], COALESCE(CONVERT(NVARCHAR(21), rtrim(LOTNUMBR)), '') AS [LotNumber], COALESCE(CONVERT(DATETIME, EXPNDATE), 0) AS [ExpirationDate], CONVERT(DECIMAL(28,10), a.UNITCOST) AS [UnitCost], CONVERT(DECIMAL(28,10), CURRCOST) AS [CurrentCost], CONVERT(DECIMAL(28,10), STNDCOST) AS [StandardCost], a.RCTSEQNM AS [ReceiptSEQNumber] FROM [TWO].dbo.IV10200 a
INNER JOIN IV00101 item ON a.ITEMNMBR = item.ITEMNMBR
LEFT JOIN IV00300 b ON a.TRXLOCTN = b.LOCNCODE AND a.DATERECD = b.DATERECD AND a.ITEMNMBR = b.ITEMNMBR AND a.RCTSEQNM = b.RCTSEQNM
LEFT JOIN IV00200 c ON a.TRXLOCTN = c.LOCNCODE AND a.DATERECD = c.DATERECD AND a.ITEMNMBR = c.ITEMNMBR AND a.RCTSEQNM = c.RCTSEQNM
WHERE RCPTSOLD = 0
ORDER BY[No], [Location], [DateReceived]
Take a look at the results to find the record(s) with a null quantity. This should help you to find the item/value causing the error, so you can fix the data prior to testing the migration again.
As a side note, here’s a link to our GP to BC Migration landing page where you’ll find all sorts of great information:
Landing Page for GP to BC Migration Information - Dynamics 365 Business Central Community
To answer your question about how many tables you should expect to see in the successful box: It really depends on what data exists in the company you're migrating from. This will vary from migration to migration. With that said, here's a blog that talks about which GP SQL tables the tool looks at, along with where the GP data lands in the BC staging tables:
Hope this helps!
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 290,524 Super User 2024 Season 2
Martin Dráb 228,469 Most Valuable Professional
nmaenpaa 101,148