[SOLVED] Using Biml to query a table with alias column name

Using Biml to query a table with alias column name

When I used Biml to generate a script for OledbSource, I got the error message like

This may be due to the 'ErrorCode', 'ErrorColumn', 'ErrorCode - Description', and 'ErrorColumn - Description' columns that are automatically added to error output paths by SSIS. Ensure that all column names are unique

I figured out the reason was I have same column name like 'ErrorCode','ErrorColumn' in my table. So I was trying to give alias for them, 'MyErrorCode' for example.

So how could I add alias?

 DirectInput>
     SELECT <#=tbl.GetColumnList("a")#> 
     FROM <#=tbl.SchemaQualifiedName#> a WITH (NOLOCK) 
     ORDER BY <#=tbl.GetColumnList(c => c.IsUsedInPrimaryKey,"a")#>
 </DirectInput>

Solution

Freehanding this answer at the moment...

Assuming the rendered query looks something like

SELECT a.col1, a.col2, a.ErrorCode

I would modify the Biml to look like

SELECT <#=tbl.GetColumnList("a").Replace("a.ErrorCode", "a.ErrorCode as MyErrorCode")#>

This would replace the string a.ErrorCode which is part of the GetColumnList output with a.ErrorCode as MyErrorCode

Also, I think the results of GetColumnList returns a list so you'll need to convert to a string before using the Replace method.

String.Join(",", tbl.GetColumnList("a")).Replace("a.ErrorCode", "a.ErrorCode as MyErrorCode")