Problem

When executing an SSIS package, the following error appears:

0x80040E55 Column does not exists

 

Solution

I encountered this error when running a SSIS package that executes a stored procedure, inside an OLE DB Source component, on several remote servers. The result set obtained is saved into a local table.

The problem is that when using stored procedures as the source of data for the SSIS pipeline, even if you can preview the data returned from the stored procedure, when pressing the columns mapping tab of the OLE DB Source Adapter, the columns are not displayed.

 

...

 

To correctly display the column, you have to add at the top of the stored procedure, as part of the procedure code, SET NOCOUNT ON, followed by a select statement used to publish the metadata.

 

You can see below an example of the stored procedure?s code:

CREATE PROCEDURE [dbo].[PROCEDURE_NAME]
AS

SET NOCOUNT ON

    IF 1 = 0
        BEGIN
            SELECT   CAST(NULL AS nvarchar(128)) AS [Column_name_1],
                     CAST(NULL AS nvarchar(128)) AS [Column_name_2],
                     CAST(NULL AS nvarchar(4000)) AS [Column_name_3],
					 CAST(NULL AS nvarchar(128)) AS [Column_name_4],
					 CAST(NULL AS int) AS [Column_name_5]
        END -- Publish metadata for diplaying columns in OLE DB Source 


select		Column_name_1,
			Column_name_2,
			Column_name_3,
			Column_name_4,
			Column_name_5
FROM TABLE_NAME

GO

Although, the column display issue is solved by adding the metadata select statement at the top of the stored procedure, when executing the package, the ?0x80040E55 Column does not exists? error is displayed and data is not imported into the local table.

In my case, the problem was that the number of columns of the metadata select was not the same as the number of columns returned by the select thau should return the actual data. So, if you encounter the same issue, make sure that the two select statements have the same number of columns. Also make sure that the columns have the same name and data type on both select statements.

 

 


No feedback yet