SSIS BIML generates SQL code with brackets

3
votes

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?

2
sqlsql-serverssisinformixbiml
Not familiar with BIML & Informix: Is there anything that's preventing you from manually removing the brackets before running the query? – Cahit
Well i have almost 700 packages, so its not really an option. – user1810355

2 Answers

2
votes

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.

0
votes

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>