Here’s a comprehensive solution covering data cleaning, import script optimization, and proper null handling:
1. Data Cleaning Strategy:
Implement a multi-stage cleaning process before the main import:
-- Pseudocode - Data cleaning pipeline:
1. Load raw data into PurchOrderStaging table
2. Identify records with NULL required fields (DeliveryDate, VendorAccount, etc.)
3. Apply business rules to populate missing values
4. Flag records that can't be auto-corrected for manual review
5. Generate data quality report
6. Only proceed with import if validation pass rate > 95%
Create a staging table with validation columns:
CREATE TABLE PurchOrderStaging (
StagingId BIGINT IDENTITY PRIMARY KEY,
PurchId NVARCHAR(20),
VendorAccount NVARCHAR(20),
DeliveryDate DATETIME,
SourceDeliveryDate DATETIME, -- Original value
DeliveryDateSource VARCHAR(50), -- Track how it was populated
ValidationStatus VARCHAR(20) DEFAULT 'Pending',
ValidationErrors NVARCHAR(500)
);
2. Null Handling Implementation:
Implement the fallback hierarchy for null delivery dates:
CREATE PROCEDURE sp_PopulateDeliveryDates
AS
BEGIN
-- Step 1: Keep valid existing dates
UPDATE PurchOrderStaging
SET DeliveryDate = SourceDeliveryDate,
DeliveryDateSource = 'Source System'
WHERE SourceDeliveryDate IS NOT NULL
AND SourceDeliveryDate >= GETDATE();
-- Step 2: Calculate from vendor lead time
UPDATE ps
SET ps.DeliveryDate = DATEADD(day, v.LeadTime, GETDATE()),
ps.DeliveryDateSource = 'Vendor Lead Time'
FROM PurchOrderStaging ps
INNER JOIN VendTable v ON ps.VendorAccount = v.AccountNum
WHERE ps.DeliveryDate IS NULL
AND v.LeadTime > 0;
-- Step 3: Calculate from item lead time
UPDATE ps
SET ps.DeliveryDate = DATEADD(day, i.LeadTime, GETDATE()),
ps.DeliveryDateSource = 'Item Lead Time'
FROM PurchOrderStaging ps
INNER JOIN PurchLineStaging pls ON ps.StagingId = pls.HeaderStagingId
INNER JOIN InventTable i ON pls.ItemId = i.ItemId
WHERE ps.DeliveryDate IS NULL
AND i.LeadTime > 0;
-- Step 4: Use system default
UPDATE PurchOrderStaging
SET DeliveryDate = DATEADD(day, 10, GETDATE()),
DeliveryDateSource = 'System Default'
WHERE DeliveryDate IS NULL;
END;
3. Enhanced Import Script:
Rewrite the import script with proper null handling and validation:
CREATE PROCEDURE sp_ImportPurchaseOrders
AS
BEGIN
-- Pre-import validation
EXEC sp_ValidatePurchaseOrderData;
-- Populate missing delivery dates
EXEC sp_PopulateDeliveryDates;
-- Main import with error handling
BEGIN TRY
BEGIN TRANSACTION;
-- Insert purchase order headers
INSERT INTO PurchTable (
PurchId, VendorAccount, DeliveryDate,
OrderDate, PurchStatus, CreatedDateTime
)
SELECT
ps.PurchId,
ps.VendorAccount,
ISNULL(ps.DeliveryDate, DATEADD(day, 10, GETDATE())),
ISNULL(ps.OrderDate, GETDATE()),
0, -- Open status
GETDATE()
FROM PurchOrderStaging ps
WHERE ps.ValidationStatus = 'Valid'
AND NOT EXISTS (
SELECT 1 FROM PurchTable pt WHERE pt.PurchId = ps.PurchId
);
-- Log import results
INSERT INTO ImportAuditLog (ImportDate, RecordsProcessed, RecordsImported)
SELECT GETDATE(), COUNT(*), @@ROWCOUNT FROM PurchOrderStaging;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
-- Log error details
INSERT INTO ImportErrorLog (ErrorDate, ErrorMessage, ErrorDetails)
VALUES (GETDATE(), ERROR_MESSAGE(), ERROR_PROCEDURE());
THROW;
END CATCH
END;
4. Comprehensive Validation Procedure:
Create a validation stored procedure that checks all required fields:
CREATE PROCEDURE sp_ValidatePurchaseOrderData
AS
BEGIN
-- Check for NULL vendor accounts
UPDATE PurchOrderStaging
SET ValidationStatus = 'Invalid',
ValidationErrors = 'Vendor account is required'
WHERE VendorAccount IS NULL OR VendorAccount = '';
-- Check for invalid vendor accounts
UPDATE ps
SET ValidationStatus = 'Invalid',
ValidationErrors = 'Vendor account not found in VendTable'
FROM PurchOrderStaging ps
WHERE NOT EXISTS (SELECT 1 FROM VendTable v WHERE v.AccountNum = ps.VendorAccount)
AND ValidationStatus = 'Pending';
-- Check for duplicate PurchId
UPDATE ps
SET ValidationStatus = 'Invalid',
ValidationErrors = 'Duplicate purchase order ID'
FROM PurchOrderStaging ps
WHERE EXISTS (
SELECT 1 FROM PurchTable pt WHERE pt.PurchId = ps.PurchId
)
AND ValidationStatus = 'Pending';
-- Mark valid records
UPDATE PurchOrderStaging
SET ValidationStatus = 'Valid'
WHERE ValidationStatus = 'Pending';
-- Generate validation summary
SELECT
ValidationStatus,
COUNT(*) as RecordCount,
STRING_AGG(ValidationErrors, '; ') as CommonErrors
FROM PurchOrderStaging
GROUP BY ValidationStatus;
END;
5. Configuration Management:
Store configurable parameters in a dedicated table:
CREATE TABLE PurchaseImportConfig (
ConfigKey NVARCHAR(50) PRIMARY KEY,
ConfigValue NVARCHAR(200),
Description NVARCHAR(500)
);
INSERT INTO PurchaseImportConfig VALUES
('DefaultLeadTimeDays', '10', 'Default delivery lead time when vendor/item lead time not available'),
('MinDeliveryDate', '0', 'Minimum days from today for delivery date'),
('MaxDeliveryDate', '365', 'Maximum days from today for delivery date'),
('ValidationPassThreshold', '95', 'Minimum % of valid records to proceed with import');
Use these parameters in your procedures:
DECLARE @DefaultLeadTime INT;
SELECT @DefaultLeadTime = CAST(ConfigValue AS INT)
FROM PurchaseImportConfig
WHERE ConfigKey = 'DefaultLeadTimeDays';
6. Detailed Error Reporting:
Generate comprehensive reports for failed imports:
CREATE PROCEDURE sp_GenerateImportErrorReport
@ImportDate DATE
AS
BEGIN
SELECT
ps.PurchId,
ps.VendorAccount,
ps.SourceDeliveryDate,
ps.ValidationStatus,
ps.ValidationErrors,
CASE
WHEN ps.VendorAccount IS NULL THEN 'Missing vendor account'
WHEN ps.SourceDeliveryDate IS NULL THEN 'Missing delivery date - populated with ' + ps.DeliveryDateSource
ELSE 'Other validation error'
END as ErrorCategory
FROM PurchOrderStaging ps
WHERE CAST(ps.CreatedDateTime AS DATE) = @ImportDate
AND ps.ValidationStatus IN ('Invalid', 'Warning')
ORDER BY ErrorCategory, ps.PurchId;
END;
7. Monitoring and Alerts:
Set up automated monitoring:
CREATE PROCEDURE sp_MonitorImportHealth
AS
BEGIN
DECLARE @FailureRate DECIMAL(5,2);
SELECT @FailureRate =
CAST(SUM(CASE WHEN ValidationStatus = 'Invalid' THEN 1 ELSE 0 END) AS DECIMAL(5,2)) / COUNT(*) * 100
FROM PurchOrderStaging
WHERE CAST(CreatedDateTime AS DATE) = CAST(GETDATE() AS DATE);
IF @FailureRate > 5.0
BEGIN
-- Send alert (integrate with your monitoring system)
RAISERROR('Purchase order import failure rate exceeds 5%%: %.2f%%', 16, 1, @FailureRate);
END
END;
8. Best Practices:
- Always use staging tables for imports - never insert directly into production tables
- Implement the fallback hierarchy for all required fields, not just DeliveryDate
- Log the source of calculated values for audit trail
- Run validation before import to catch issues early
- Generate detailed error reports for business users to correct source data
- Schedule imports during low-usage periods to minimize lock contention
- Archive staging data for 30 days for troubleshooting
- Test import procedures in sandbox after every D365 update
- Document your data quality rules and fallback logic
9. Schema Change Detection:
Create a procedure to detect schema changes after updates:
CREATE PROCEDURE sp_CheckPurchTableSchema
AS
BEGIN
SELECT
c.name as ColumnName,
t.name as DataType,
c.is_nullable,
c.max_length
FROM sys.columns c
INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID('PurchTable')
AND c.name IN ('DeliveryDate', 'ConfirmedDlv', 'VendorAccount', 'PurchStatus');
END;
Run this after every D365 update to identify schema changes that might affect your import.
This comprehensive approach ensures robust null handling, clear audit trails, and minimal import failures even after schema changes in D365 updates.