Код ошибки 0x80040e14

I run the SSIS 2012 package and got following error.

SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E14.  An OLE DB record is available.  Source: «Microsoft SQL Server Native Client 11.0»  Hresult: 0x80040E14  Description: «Could not bulk load because
SSIS file mapping object ‘GlobalDTSQLIMPORT              ‘ could not be opened. Operating system error code 5(Access is denied.). Make sure you are accessing a local server via Windows security.»

I saw similar posts. The solution would be — set “Local Security Policy” — «create global objects».
I run the package by ‘Administrator’ user that permitted the «create global objects».

In the Connection manager, the authentication is ‘SQL Server Authentication’ and specified ‘sa’ user.

It is not remote environment. Run the package on the Server machine.

If I changed to Windows Authentication in the connection manager, it works fine.
If I switched OLD DB destination, the package works fine.

Any idea to solve this issue to keep ‘SQL Server destination’ and ‘SQL Server authentication’?

I have an ETL package built in SSIS that I’m trying to run but I’m getting this error, mainly the third one:

The part in the package that is giving the error is built like this:

The specific component that is causing the error is the call to the SP and this are the parameters:

The parameters are translated to:

The parameters come from the query done at the start of the data flow:

The error mentions invalid column ‘P2’ the only column that takes this value is SG_PLANO_CTB.

This is the SP that’s being used:

USE [SISF_DW_REPORTING]
GO
/****** Object:  StoredProcedure [dbo].[SP_INSERT_EAF_MEMBER]    Script Date: 8/10/2018 11:22:07 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Batch submitted through debugger: SQLQuery64.sql|7|0|C:UsersSQL_AD~1.CMCAppDataLocalTemp3~vs4CBB.sql
-- Batch submitted through debugger: SQLQuery29.sql|7|0|C:UsersSQL_AD~1.CMCAppDataLocalTemp3~vs8A81.sql
-- =============================================
-- Author:      
-- Create date: 
-- Description: This stored procedure creates the Early Arriving Fact for a given reference table
-- =============================================
ALTER PROCEDURE [dbo].[SP_INSERT_EAF_MEMBER]
     @TABLE                 NVARCHAR(50)
    ,@CD_REF                NVARCHAR(50)
    ,@LOG_ID                INT
    ,@ID_SK_COMPOSITE       NVARCHAR(50) = NULL
    ,@COL_SK_COMPOSITE      NVARCHAR(50) = NULL
    ,@DT_LOAD_DATE          INT = NULL
    ,@TABLE_FCT             NVARCHAR(50) = NULL
    ,@DEBUG                 BIT = 0

AS
BEGIN
    SET NOCOUNT ON

    IF 1=2
        BEGIN
            SELECT  CAST(NULL AS INT) AS ID_REF
                   ,CAST(NULL AS NVARCHAR(50)) AS DS_TBL_NAME
                   ,CAST(NULL AS NVARCHAR(50)) AS CD_SRCE_SYTM
                   ,CAST(NULL AS INT) AS LOG_ID
                   ,CAST(NULL AS INT) AS DT_LOAD_DATE
                   ,CAST(NULL AS NVARCHAR(50)) AS DS_TBL_FCT_NAME
        END
    --****************************************************
    SET @TABLE      = LTRIM(RTRIM(@TABLE))
    SET @CD_REF     = LTRIM(RTRIM(@CD_REF)) 
    SET @TABLE_FCT  = LTRIM(RTRIM(@TABLE_FCT))
    --****************************************************

    DECLARE @ID_INT_EAF INT
    DECLARE @ID_EAF NVARCHAR(100)
    DECLARE @DS_EAF NVARCHAR(100)
    DECLARE @DT_INT_EAF NVARCHAR(100)
    DECLARE @DT_DAT_EAF NVARCHAR(100)
    DECLARE @CD_INT_EAF NVARCHAR(100)
    DECLARE @CD_VAR_EAF NVARCHAR(100)
    DECLARE @FL_EAF NVARCHAR(4)
    DECLARE @NR_INT_EAF NVARCHAR(100)
    DECLARE @NR_VAR_EAF NVARCHAR(100)
    DECLARE @QT_EAF NVARCHAR(100)
    DECLARE @VL_EAF NVARCHAR(100)
    DECLARE @DMS_EAF NVARCHAR(100)
    DECLARE @GPS_EAF NVARCHAR(100)
    DECLARE @WGS_EAF NVARCHAR(100)
    DECLARE @DT_CRTN NVARCHAR(100)
    DECLARE @QUERY_FCT_LOAD_EAF VARCHAR(7000)
    DECLARE @QUERY_REF_EAF VARCHAR(7000)
    DECLARE @SG_EAF NVARCHAR(100)
    DECLARE @HR_EAF NVARCHAR(100)

    SET @ID_EAF = '-1'
    SET @DS_EAF = '''EAF Member ('+@CD_REF+')'''
    SET @DT_INT_EAF = '-1'
    SET @DT_DAT_EAF = '''1900-01-01'''
    SET @CD_VAR_EAF = ''''+@CD_REF+''''
    SET @CD_INT_EAF = '-1'
    SET @FL_EAF = '''-1'''
    SET @NR_INT_EAF = '0'
    SET @NR_VAR_EAF = '''EAF Member'''
    SET @QT_EAF = '''0'''
    SET @VL_EAF = '''0'''
    SET @DMS_EAF = '''EAF Member'''
    SET @GPS_EAF = '''EAF Member'''
    SET @WGS_EAF = '-1'
    SET @DT_CRTN = CONVERT(NVARCHAR(8),GETDATE(),112)
    SET @SG_EAF = '''EAF'''
    SET @HR_EAF = '''00:00:00'''

    -- Declare auxiliary variables
    DECLARE @TABLE_NAME NVARCHAR(50), @COLUMN_NAME NVARCHAR(100), @EAF_VALUE NVARCHAR(100)
    DECLARE @INSERT NVARCHAR(3000), @VALUES NVARCHAR(3000), @WHERE NVARCHAR(1000), @IDENTITY_ON NVARCHAR(1000), @IDENTITY_OFF NVARCHAR(1000)
    DECLARE @STATEMENT VARCHAR(7000)

    SET @IDENTITY_ON = 'SET IDENTITY_INSERT ' + @TABLE + ' ON;'
    SET @IDENTITY_OFF = 'SET IDENTITY_INSERT ' + @TABLE + ' OFF;'
    SET @INSERT = 'INSERT INTO ' + @TABLE + ' ('
    SET @VALUES = ' SELECT '


        BEGIN
            IF @COL_SK_COMPOSITE IS NULL
                SET @WHERE  = ' WHERE NOT EXISTS (SELECT 1 FROM ' + @TABLE + ' WHERE CD_' + SUBSTRING(@TABLE,9,LEN(@TABLE)) + ' = '''+@CD_REF+''');'
            ELSE
                SET @WHERE  = ' WHERE NOT EXISTS (SELECT 1 FROM ' + @TABLE + ' WHERE CD_' + SUBSTRING(@TABLE,9,LEN(@TABLE)) + ' = '''+@CD_REF+''' AND ' + @COL_SK_COMPOSITE + ' = ' + @ID_SK_COMPOSITE + ');'
        END

    DECLARE TABLE_COLUMNS CURSOR FOR  
    SELECT 
         C.TABLE_NAME
        ,C.COLUMN_NAME
        ,CASE
            WHEN @COL_SK_COMPOSITE IS NOT NULL AND LEFT(C.NEW_COLUMN_NAME,2) LIKE 'ID' AND C.NEW_COLUMN_NAME NOT LIKE 'ID_'+SUBSTRING(C.TABLE_NAME,5,LEN(C.TABLE_NAME)) AND C.NEW_COLUMN_NAME NOT LIKE 'ID_'+SUBSTRING(C.TABLE_NAME,9,LEN(C.TABLE_NAME)) 
            THEN @ID_SK_COMPOSITE
            WHEN @COL_SK_COMPOSITE IS NULL AND LEFT(C.NEW_COLUMN_NAME,2) LIKE 'ID' AND C.NEW_COLUMN_NAME NOT LIKE 'ID_'+SUBSTRING(C.TABLE_NAME,5,LEN(C.TABLE_NAME)) AND C.NEW_COLUMN_NAME NOT LIKE 'ID_'+SUBSTRING(C.TABLE_NAME,9,LEN(C.TABLE_NAME)) 
            THEN @ID_EAF
            WHEN LEFT(C.NEW_COLUMN_NAME,2) LIKE 'DS'
                THEN @DS_EAF
            WHEN C.NEW_COLUMN_NAME = 'DT_START' THEN '''1900-01-01'''
            WHEN C.NEW_COLUMN_NAME = 'DT_END' THEN '''9999-12-31'''
            WHEN LEFT(C.NEW_COLUMN_NAME,2) LIKE 'DT' THEN
                CASE
                    WHEN C.NEW_COLUMN_NAME LIKE 'DT_CRTN' THEN @DT_CRTN
                    WHEN C.DATA_TYPE LIKE 'int' THEN @DT_INT_EAF 
                    ELSE @DT_DAT_EAF END
            WHEN LEFT(C.NEW_COLUMN_NAME,2) LIKE 'CD' THEN
                CASE WHEN C.DATA_TYPE LIKE 'int' THEN @CD_INT_EAF ELSE @CD_VAR_EAF END
            WHEN LEFT(C.NEW_COLUMN_NAME,2) LIKE 'FL'
                THEN @FL_EAF
            WHEN LEFT(C.NEW_COLUMN_NAME,2) LIKE 'NR' THEN
                CASE 
                    WHEN C.DATA_TYPE LIKE 'int' THEN @NR_INT_EAF
                    WHEN C.DATA_TYPE LIKE 'numeric' THEN @NR_INT_EAF 
                    ELSE @NR_VAR_EAF END
            WHEN LEFT(C.NEW_COLUMN_NAME,2) LIKE 'QT'
                THEN @QT_EAF
            WHEN LEFT(C.NEW_COLUMN_NAME,2) LIKE 'VL'
                THEN @VL_EAF
            WHEN LEFT(C.NEW_COLUMN_NAME,3) LIKE 'DMS'
                THEN @DMS_EAF
            WHEN LEFT(C.NEW_COLUMN_NAME,3) LIKE 'GPS'
                THEN @GPS_EAF
            WHEN LEFT(C.NEW_COLUMN_NAME,3) LIKE 'WGS'
                THEN @WGS_EAF
            WHEN C.NEW_COLUMN_NAME = 'CTL_LOG_EAF'
                THEN '1'
            WHEN LEFT(C.NEW_COLUMN_NAME,7) LIKE 'CTL_LOG'
                THEN CAST(@LOG_ID AS NVARCHAR(50))
            WHEN @COL_SK_COMPOSITE IS NOT NULL AND LEFT(C.NEW_COLUMN_NAME,2) LIKE 'SG'
                THEN @ID_SK_COMPOSITE
            WHEN @COL_SK_COMPOSITE IS NULL AND LEFT(C.NEW_COLUMN_NAME,2) LIKE 'SG'
                THEN @SG_EAF
            WHEN LEFT(C.NEW_COLUMN_NAME,2) LIKE 'HR'
                THEN @HR_EAF
            ELSE '' 
            END EAF_VALUE
    FROM
        (
            SELECT
                 TABLE_NAME
                ,COLUMN_NAME
                ,CASE WHEN LEFT(COLUMN_NAME,2) LIKE 'X_' THEN SUBSTRING(COLUMN_NAME,3,LEN(COLUMN_NAME)) ELSE COLUMN_NAME END AS NEW_COLUMN_NAME
                ,DATA_TYPE
                ,ORDINAL_POSITION
            FROM
                INFORMATION_SCHEMA.COLUMNS
            WHERE COLUMN_NAME NOT LIKE 'ID_'+SUBSTRING(TABLE_NAME,9,LEN(TABLE_NAME))
        ) C
        INNER JOIN
        INFORMATION_SCHEMA.TABLES T 
            ON C.TABLE_NAME = T.TABLE_NAME
    WHERE 
        T.TABLE_TYPE LIKE 'BASE TABLE'
        AND T.TABLE_NAME LIKE @TABLE
    ORDER BY 
        TABLE_NAME, ORDINAL_POSITION

    OPEN TABLE_COLUMNS   
    FETCH NEXT FROM TABLE_COLUMNS INTO @TABLE_NAME, @COLUMN_NAME, @EAF_VALUE; 

    WHILE @@FETCH_STATUS = 0  

    BEGIN   
        IF @COLUMN_NAME <> ''
            BEGIN
                SET @INSERT = @INSERT + @COLUMN_NAME + ', '
            END

        IF @EAF_VALUE <> ''
            BEGIN
                SET @VALUES = @VALUES + @EAF_VALUE + ', '
            END

        FETCH NEXT FROM TABLE_COLUMNS INTO @TABLE_NAME, @COLUMN_NAME, @EAF_VALUE; 
    END

    CLOSE TABLE_COLUMNS   
    DEALLOCATE TABLE_COLUMNS

    -- Final columns
    SET @INSERT = LEFT(@INSERT,LEN(@INSERT)-1) + ')'
    SET @VALUES = LEFT(@VALUES,LEN(@VALUES)-1)

    SET @QUERY_FCT_LOAD_EAF = 'INSERT INTO FCT_LOAD_EAF (ID_LOAD_DATE, ID_CRTN_DATE, DS_REF_TABLE_NAME, DS_FCT_TABLE_NAME, CD_SRCE_SYTM,CTL_LOG_INSERT) SELECT '+CAST(@DT_LOAD_DATE AS NVARCHAR(8))+', '+@DT_CRTN+', '''+@TABLE+''', '''+@TABLE_FCT+''', '''+@CD_REF+''', '+CAST(@LOG_ID AS NVARCHAR(50))+' '+@WHERE
    IF @DEBUG = 1 BEGIN SELECT @QUERY_FCT_LOAD_EAF END ELSE     BEGIN EXECUTE(@QUERY_FCT_LOAD_EAF) END
    PRINT @QUERY_FCT_LOAD_EAF

    SET @STATEMENT = @INSERT + @VALUES + @WHERE
    IF @DEBUG = 1 BEGIN SELECT @STATEMENT END ELSE  BEGIN EXECUTE(@STATEMENT) END 
    PRINT @STATEMENT

    IF LEFT(@TABLE,2) = 'X_'
        BEGIN
            SET @QUERY_REF_EAF = 'SELECT X_ID_'+SUBSTRING(@TABLE,7,LEN(@TABLE))+' AS ID_REF, ''' + @TABLE + ''' AS DS_TBL_NAME, CAST(X_CD_SRCE_SYTM AS NVARCHAR(50)) AS CD_SRCE_SYTM, '+CAST(@LOG_ID AS NVARCHAR(50))+' AS LOG_ID, '+CAST(@DT_LOAD_DATE AS NVARCHAR(8))+' AS DT_LOAD_DATE , ''' + @TABLE_FCT + ''' AS DS_TBL_FCT_NAME FROM '+@TABLE+' WHERE X_CD_SRCE_SYTM LIKE '''+@CD_REF+''''
            IF @DEBUG = 1 BEGIN SELECT @QUERY_REF_EAF END ELSE  BEGIN EXECUTE(@QUERY_REF_EAF) END
            PRINT @QUERY_REF_EAF
        END
    ELSE
        BEGIN
            SET @QUERY_REF_EAF = 'SELECT ID_'+SUBSTRING(@TABLE,5,LEN(@TABLE))+' AS ID_REF, ''' + @TABLE + ''' AS DS_TBL_NAME, CAST(CD_SRCE_SYTM AS NVARCHAR(50)) AS CD_SRCE_SYTM, '+CAST(@LOG_ID AS NVARCHAR(50))+' AS LOG_ID, '+CAST(@DT_LOAD_DATE AS NVARCHAR(8))+' AS DT_LOAD_DATE, ''' + @TABLE_FCT + ''' AS DS_TBL_FCT_NAME FROM '+@TABLE+' WHERE CD_SRCE_SYTM LIKE '''+@CD_REF+''''
            IF @DEBUG = 1 BEGIN SELECT @QUERY_REF_EAF END ELSE  BEGIN EXECUTE(@QUERY_REF_EAF) END
            PRINT @QUERY_REF_EAF
        END

    SET NOCOUNT OFF
END

I tried debugging the SP but I can’t figure out where he builds the query that uses ‘P2’ as a column and not as value of the column SG_PLANO_CTB

Edit: I decided to log the parameters that were being used. Found out that the one causing the call causing the error is

exec SISF_DW_REPORTING..SP_INSERT_EAF_MEMBER 'REF_FIN_RUBRICA','11.1.1', 210999, 'P2', 'SG_PLANO_CTB'

And the query that’s causing the error is

INSERT INTO REF_FIN_RUBRICA (CD_RUBRICA, DS_RUBRICA, CD_KEY, CD_PARENT, SG_PLANO_CTB, DT_START, DT_END, CTL_LOG_UPDATE, CTL_LOG_EAF) SELECT '11.1.1', 'EAF Member (11.1.1)', '11.1.1', '11.1.1', P2, '1900-01-01', '9999-12-31', 210999, 1 WHERE NOT EXISTS (SELECT 1 FROM REF_FIN_RUBRICA WHERE CD_RUBRICA = '11.1.1' AND SG_PLANO_CTB = P2);

I would guess the string delimitators aren’t being added in the cursor somewhere. Can’t see where though.