SSIS BIML generates SQL code with brackets

问题:

Im using BIML to dynamically create load packages for SSIS to load data from Informix to SQL Server. The problem is that this BIML code produces the SQL below

<DirectInput>SELECT <#=table.GetColumnList()#> FROM <#=table.GetTag("SourceSchemaQualifiedName")#></DirectInput>

SELECT [column1], [column2], [column3], FROM [mySchema].[mySrcTable]

But that doesnt work in my source database because of the brackets. Any way i can get the columnlist & tablename without the brackets dynamically?

回答1:

You should be able to use the overloaded method of GetColumnList

<#=table.GetColumnList(string.Empty, "\"", "\"")#>

which should produce a double quote wrapped column name with no table alias - which I think is what Informix expects.

回答2:

This works for both column names and table name:

                        <OdbcSource Name="Data from informix" Connection="Source_Informix">
                            <DirectInput>SELECT <#=table.GetColumnList(string.Empty, "", "")#> FROM schema.<#=table.Name#></DirectInput>
                        </OdbcSource>
                        <OleDbDestination Name="Data to MSSQL" ConnectionName="Target_MSSQL">
                            <TableOutput TableName="<#=table.ScopedName#>"/>
                        </OleDbDestination>