Add "Numeric" rule to SQLSERVER connection type in Collect.

In the absence of a Numeric data type rule, Collect defaults to the ANY type as defined as nvarchar with the same length as the source field. When the source data contains a value that is at the character maximum for numeric and is a negative value, this value will cause an error due to character length when attempting to import. I suggest adding a numeric data type rule to connection types that is formatted the same way as the decimal data type rule. Below I've pasted a script that performs this task:

 


USE [DataGarage]
GO

INSERT INTO [dbo].[ztTargetSourceConnectionTypeDataConversion]
([TargetConnectionType]
,[SourceConnectionType]
,[ProcessOrder]
,[DataValidateControl]
,[DataType]
,[DataLength]
,[DataPrecision]
,[DataScale]
,[ProcessFlag]
,[NewDataTypeFormat]
,[NewDataType]
,[NewDataLength]
,[NewDataPrecision]
,[NewDataScale]
,[ColumnOverrideTemplate]
,[DeltaDataType]
,[CranSoftSupplied]
,[AddedBy]
,[AddedOn]
,[ChangedBy]
,[ChangedOn]
,[boaStatus]
,[AddedVia]
,[ChangedVia])
USE [DataGarage]

SELECT [TargetConnectionType]
,[SourceConnectionType]
,[ProcessOrder]+1
,[DataValidateControl]
,REPLACE([DataType], 'DECIMAL', 'NUMERIC') AS DataType
,[DataLength]
,[DataPrecision]
,[DataScale]
,[ProcessFlag]
,REPLACE([NewDataTypeFormat],'DECIMAL', 'NUMERIC') AS NewDataTypeFormat
,REPLACE([NewDataType],'DECIMAL', 'NUMERIC') AS NewDataType
,[NewDataLength]
,[NewDataPrecision]
,[NewDataScale]
,[ColumnOverrideTemplate]
,[DeltaDataType]
,[CranSoftSupplied]
,[AddedBy]
,[AddedOn]
,[ChangedBy]
,[ChangedOn]
,[boaStatus]
,[AddedVia]
,[ChangedVia]
FROM [dbo].[ztTargetSourceConnectionTypeDataConversion]
WHERE SourceConnectionType = 'SQLSERVER' AND DataType = 'Decimal'
GO

  • Logan Buck
  • Oct 23 2018
  • Planned
  • Attach files
  • Admin
    Jon Green commented
    12 Nov, 2018 12:14pm

    Agreed, this makes sense and has been added to the backlog of tickets for Sustain team to action.  Thanks for registering.