Database

Execution failed for SSIS Package: BillingDataLoad, Error description: FileFullName: Source Is Db, SSISPackageName: BillingDataLoad.dtsx, Source : Microsoft.SqlServer.Dts.Runtime.TaskHost, InformationCode : 1074041076, Description : Lookup has cached 16 rows. , SubComponent: Lookup [82], HelpFiledtsmsg110.rll, HelpContext0, IdofInterfaceWithError{B6F6D221-FC27-4F71-B5A0-597583986C28}

Description  {0}Excel Source failed validation and returned error code 0xC020801C.
, HelpContext  {0}0, HelpFile     {0}dtsmsg110.rll, IDOfInterfaceWithError {0}{B6F6D221-FC27-4F71-B5A0-597583986C28}, Source       {0}
Data Flow Task, Subcomponent {0}SSIS.Pipeline, Timestamp    {0}9/27/2017 5:03:34 PM, ErrorCode    {0}-1073450985
Description  {0}SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  
The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0209302.  
There may be error messages posted before this with more information on why the AcquireConnection method call failed.
, HelpContext  {0}0, HelpFile     {0}dtsmsg110.rll, IDOfInterfaceWithError {0}{B6F6D221-FC27-4F71-B5A0-597583986C28}, Source       {0}
Data Flow Task, Subcomponent {0}Excel Source [2], Timestamp    {0}9/27/2017 5:03:34 PM, ErrorCode    {0}-1071611876
Description  {0}The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. For more information, 
see http://go.microsoft.com/fwlink/?LinkId=219816
, HelpContext  {0}0, HelpFile     {0}, IDOfInterfaceWithError {0}{AC9502ED-5A99-4CB0-A467-B2AB5A157E70}, Source       {0}
ImportXlsxFiles, Subcomponent {0}Connection manager "Excel Connection Manager", Timestamp    {0}9/27/2017 5:03:34 PM, ErrorCode    {0}-1073676245
Description  {0}The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. 
If the 32-bit driver is not installed, run the package in 64-bit mode. Error code: 0x00000000.
An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  
Description: "Class not registered".
, HelpContext  {0}0, HelpFile     {0}, IDOfInterfaceWithError {0}{AC9502ED-5A99-4CB0-A467-B2AB5A157E70}, 
Source       {0}ImportXlsxFiles, Subcomponent {0}Connection manager "Excel Connection Manager", 
Timestamp    {0}9/27/2017 5:03:34 PM, ErrorCode    {0}-1071607038
Package errors count after running the package:   {0}6

And another thing I found is, when I saw the Server it is showing the Ace 12.0 drivers in the drivers list, is it corrupted or over written or I need to re register it, etc are my questions. If I can resolve this issue without much change in the Server like re-registering the Ace 12.0 Drivers etc it would be nice to do that, since those drivers are showing up in the Drivers list.
Another thing I did was I installed SSDT 2017 to check if that takes Ace 14.0 drivers by default because I thought the new Versions of Office might have over written the Ace 12.0 drivers but no SSIS still is looking only Ace 12.0 drivers for 64 bit Excel files? I am not sure is it because of the SQL Server Version or SSIS Engine Version or Visual Studio Version or do SSDT always takes only Ace 12.0 drivers for Excel I am not sure, it would be a great help for me if somebody can help me with some guidance or any link etc, any thing helps my friends. I am also searching and applying different options, if somebody has gone through the same and resolved it, it would be a great help.

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