CODEWITHSUNDEEP
sqlsql-serverssis
Peter Sun
Peter Sun

Reputation: 1835

SSIS: Column 'column_name' cannot be found at the datasource

I am working on a SSIS project. I am trying to run a query into a file. In my package, I have an OLE DB Source that runs this query.

USE [DB]

SET FMTONLY OFF;

DECLARE @table_data TABLE 
                    (
                        column_01 VARCHAR(255),
                        column_02 VARCHAR(10),
                        column_04 VARCHAR(255)
                    )

SELECT 
    'column_01',
    'column_02',
    'column_03'
UNION ALL 
        column_01,
        column_02,
        column_03
FROM 
    @table_data

When I try to run the script, I get this error:

SSIS: Column 'column_01' cannot be found at the datasource

I tried setting the ValidateExternalMetaData on and I get an error (failed validation and returned validation status "VS_NEEDSNEWMETADATA").

The OLE DB Source is dumping the data into a dynamic text file.

I don't know how to fix this or understand why SSIS is not allowing me to use this.

Upvotes: 1

Views: 4213

Answers (3)

masoud
masoud

Reputation: 478

after union you must use select like this: 

USE [DB]

SET FMTONLY OFF;

DECLARE @table_data TABLE 
                    (
                        column_01 VARCHAR(255),
                        column_02 VARCHAR(10),
                        column_04 VARCHAR(255)
                    )

SELECT 
    'column_01',
    'column_02',
    'column_03'

UNION ALL 

select
        column_01,
        column_02,
        column_03
FROM 
    @table_data

Upvotes: 1

Khairul Alam
Khairul Alam

Reputation: 1346

I tried like bellow. It is working without any error.

USE [DB]

SET FMTONLY OFF;

DECLARE @table_data TABLE 
                    (
                        column_01 VARCHAR(255),
                        column_02 VARCHAR(10),
                        column_04 VARCHAR(255)
                    )

INSERT INTO @table_data
SELECT 'column_01','column_02','column_03'

SELECT * FROM @table_data

enter image description hereenter image description here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Your code does not generate an error; see here.

I suspect, though, that you are expecting your results to have columns with names. But they are just anonymous strings. What you want is rather unclear. Perhaps:

SELECT 'column_01' as column_01, 'column_02' as column_02,
       'column_03' as column_03
FROM @table_data;

Or perhaps you want the values from the table rather than constant string values:

SELECT column_01, column_02,column_03
FROM @table_data;

Upvotes: 1

Related Questions