Database

FileFullName: \\xxxxxxxxxxxxxxx\xxxxxx\Prod\MemberMaint092017.xlsx, SSISPackageName: SgLgReporting_SLMemberMaint_New.dtsx, 
Source : Microsoft.SqlServer.Dts.Runtime.Package, WarningCode : -2147381246, Description : SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  
The Execution method succeeded, but the number of errors raised (5) reached the maximum allowed (1); 
resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. 
Change the MaximumErrorCount or fix the errors.
, SubComponent: , HelpFile, HelpContext-2147381246, IdofInterfaceWithError{9744DAE0-F87A-45FC-B444-89E65C74BBF6}

-- I'm using this variable to indicate when a record was inserted
DECLARE @nextOrdinal INT = (SELECT CASE WHEN MAX(InsertOrdinal) IS NULL THEN 1 
                                        ELSE MAX(InsertOrdinal)+1 
                                        END 
                            FROM [Essentris].[dbo].[VancoMycin]);

-- create a temporary table to hold our grouped/normalized data
IF OBJECT_ID('tempdb..#tempVanco') IS NOT NULL
BEGIN
    DROP TABLE #tempVanco;
END

-- this temp table holds our grouped and normalized data
CREATE TABLE #tempVanco
(
    [ABX_NAME]      [nvarchar](255) NULL,
    [ROUTE]         [nvarchar](255) NULL,
    [DELIVERY_TIME] [datetime]      NULL,
    [HOSPNO]        [int]             NULL,
    [PTNAME]        [nvarchar](255) NULL,
    [UNIT]          [nvarchar](255) NULL,
    [ATTENDING]     [nvarchar](255) NULL,
    [SERVICE]       [nvarchar](255) NULL,
    [ADX]           [nvarchar](255) NULL
);

-- Normalize the data so that there is no unexpected stuff in any of the fields. This 
-- also keeps me from having to do this further down in the code in the cte (this is a 
-- desperation measure after fighting with this for DAYS)

update  [Essentris].[dbo].[IMPORTED_Vanco]
SET     [ABX_NAME] = UPPER(RTRIM(LTRIM([ABX_NAME])))
        ,[ROUTE]    = UPPER(RTRIM(LTRIM([ROUTE])))
        ,[PTNAME]   = UPPER(RTRIM(LTRIM([PTNAME])))
        ,[UNIT]     = UPPER(RTRIM(LTRIM([UNIT])))
        ,[ATTENDING]= UPPER(RTRIM(LTRIM([ATTENDING])))
        ,[SERVICE]  = UPPER(RTRIM(LTRIM([SERVICE])))
        ,[ADX]      = UPPER(RTRIM(LTRIM([ADX])));

-- group the imported table data (the data may have duplicate rows)

;with cte as 
(
    SELECT  [ABX_NAME]
            ,[ROUTE]
            ,[DELIVERY_TIME]
            ,CAST([HOSPNO] AS INT) AS [HOSPNO]
            ,[PTNAME]
            ,[UNIT]
            ,[ATTENDING]
            ,[SERVICE]
            ,[ADX]
    FROM    [Essentris].[dbo].[IMPORTED_Vanco]
    GROUP BY [ABX_NAME]
            ,[ROUTE]
            ,[DELIVERY_TIME]
            ,CAST([HOSPNO] AS INT)
            ,[PTNAME]
            ,[UNIT]
            ,[ATTENDING]
            ,[SERVICE]
            ,[ADX]
)
-- and insert it into the temp table from the cte
insert into #tempvanco 
select * from cte;

-- Up to this point, the contents of #tempvanco is as expected

--================

-- merge #tempvanco into our concrete table. 

MERGE INTO [Essentris].[dbo].[VancoMycin] AS t
USING #tempVanco AS s
ON  
(
    -- this is really weird: if I just use the three fields below, it reinserts fewer 
    -- records than if I use ALL of the fields. This hints at there being a problem 
    -- with one or more of the string fields, but after they've been normalized this 
    -- theoretically should not be the case.
        t.[DELIVERY_TIME] = s.[DELIVERY_TIME]
    AND t.[HOSPNO]        = s.[HOSPNO]
    -- I tried using "like" instead of "=", and it had no effect
    AND t.[PTNAME]        like s.[PTNAME]
    --and t.[ABX_NAME]      = s.[ABX_NAME]
    --and t.[ROUTE]         = s.[ROUTE]
    --and t.[UNIT]          = s.[UNIT]
    --and t.[ATTENDING]     = s.[ATTENDING]
    --and t.[SERVICE]       = s.[SERVICE]
    --and t.[ADX]           = s.[ADX]
)

-- We should never need to update anything, so we only react when a record is new

WHEN NOT MATCHED BY TARGET THEN
    INSERT 
    (
        [ABX_NAME]
        ,[ROUTE]
        ,[DELIVERY_TIME]
        ,[HOSPNO]
        ,[PTNAME]
        ,[UNIT]
        ,[ATTENDING]
        ,[SERVICE]
        ,[ADX]

        -- we need to create some data to fill in these fields
        ,[ABX_NAME_SHORT]
        ,[DELIVERY_DATE]
        ,InsertOrdinal
    )
    VALUES
    (
        s.[ABX_NAME]
        ,s.[ROUTE]
        ,s.[DELIVERY_TIME]
        ,s.[HOSPNO]
        ,s.[PTNAME]
        ,s.[UNIT]
        ,s.[ATTENDING]
        ,s.[SERVICE]
        ,s.[ADX]

        -- created data
        ,'VANCOMYCIN'
        ,CONVERT(DATE, s.[DELIVERY_TIME])
        ,@nextOrdinal
    );

-- drop the temporary table

IF OBJECT_ID('tempdb..#tempVanco') IS NOT NULL 
BEGIN
    DROP TABLE #tempVanco;
END