Search code examples
integrationetlsql-server-data-tools

how to fix 'The input column specified was not found in the input column collection' in Visual Studio ssdt

I'm trying to perform ETL in Visual Studio ssdt. but getting error while entering Expression for Derived Column Name inside Derived Column Transformation Editor.

I've tried changing the Derived Column Name and Expression

Screen shot of error

Screen shot of error

Error I'm getting is -

TITLE: Microsoft Visual Studio

Error at ETL 1 [Derived Column [62]]: Attempt to find the input column named "Units_Sold" failed with error code 0xC0010009. The input column specified was not found in the input column collection.

Error at ETL 1 [Derived Column [62]]: Attempt to parse the expression "Units_Sold*Sale_Price" failed and returned error code 0xC00470A2. The expression cannot be parsed. It might contain invalid elements or it might not be well-formed. There may also be an out-of-memory error.

Error at ETL 1 [Derived Column [62]]: Cannot parse the expression "Units_Sold*Sale_Price". The expression was not valid, or there is an out-of-memory error.

Error at ETL 1 [Derived Column [62]]: The expression "Units_Sold*Sale_Price" on "Derived Column.Outputs[Derived Column Output].Columns[total_amt]" is not valid.

Error at ETL 1 [Derived Column [62]]: Failed to set property "Expression" on "Derived Column.Outputs[Derived Column Output].Columns[total_amt]".

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap)

BUTTONS:

OK

Solution

  • In a Derived Column, the columns used in an expression match exactly match the characters and case of the input columns. Since your input columns have spaces, you will need to use brackets to distinguish these. The underscore isn't necessary and the space can be kept since the column names will be enclosed as follows.

    [Units Sold] * [Sale Price]