BimlExpress: SQL comments in variables containing SQL can cause compilation failure

[DanCorder]

I'm submitting a...

[ ] Regression (a behavior that used to work and stopped working in a new release)
[X] Bug report  
[ ] Performance issue
[ ] Feature request
[ ] Documentation issue or request
[ ] Support request => Please do not submit support request here, instead go to https://varigence.com/Forums?forumName=Biml or email support@varigence.com
[ ] Other... Please describe:

Current behavior

When compiling a BIML file containing a variable that contains "--" SQL comments within a CDATA block, a validation step in the compilation of the file can fail. This appears to be because during the validation step any linebreaks are stripped from the variable text, so that any SQL after the comment becomes part of the comment.

Note that the linebreaks are not stripped out in the final compiled SSIS package.

More details here.

Expected behavior

The SQL validation should pass

Minimal reproduction of the problem with instructions

Try to compile this file:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="Source" ConnectionString="Provider=SQLNCLI11;Data Source=localhost\dev2017;Integrated Security=SSPI;Initial Catalog=tempdb;" />
    </Connections>
    <Packages>
        <Package Name="SO_51407483" ConstraintMode="Linear">
            <Variables>
                <Variable Name="querySql" DataType="String" IncludeInDebugDump="Exclude" EvaluateAsExpression="true">
                  <![CDATA["SELECT --This is a comment
1 AS Col1"]]>
                </Variable>
            </Variables>
            <Tasks>
                <Dataflow Name="DFT Demo">
                    <Transformations>
                        <OleDbSource Name="OLESRC query" ConnectionName="Source">
                            <VariableInput VariableName="User.querySql" />
                        </OleDbSource>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>

What is the motivation / use case for changing the behavior?

I would like to be able to write SQL in variables without having to escape "<" and ">"

Environment

BimlExpress version: 5.0.63025.0


Visual Studio Version:

- [ ] 2005
- [ ] 2008
- [ ] 2010
- [ ] 2012
- [ ] 2013
- [ ] 2015
- [X] 2017

Target SSIS Version:

- [ ] 2005
- [ ] 2008
- [ ] 2008 R2
- [ ] 2012
- [X] 2014
- [ ] 2016
- [ ] 2017

[jalley3]

What seems to be happening is that the newline character is getting stripped out of the CDATA section. We are investigating why this is happening, but I believe in the mean time you can put a "\n" character at the end of your comments and it should work.

[DanCorder]

Please note that the newline appears to only be stripped out for the validation step. As far as I can tell it is not stripped out when generating the actual output package. See the linked stackoverflow question for my experiments.