How can I prevent BIML Express quoting an ODBC Driver

Question

TL;DR

I'd like to prevent BIML from wrapping my ODBC driver in double quotes when it creates my dtsx package.

More info

I have a very simple BIML file, shown below after the BIML code header, which connects to Composite Information Server (CIS). I don't think the datasource is important, but I thought I'd include in case it's pertinent to the question/problem.

The CIS ODBC driver is installed with a driver name of Cisco Information Server 7.0.

As below, I'm entering the driver name correctly, but when BIML generates the dtsx package, it wraps quotes round the name, so, for a connection like this:

<OdbcConnection Name="CIS" ConnectionString="Driver={Cisco Information Server 7.0};Server=xxxxxxxxxx;Port=xxxxxxxxxx;Domain=xxxxxxxxxx;dataSource=xxxxxxxxxx;database=src;User=xxxxxxxxxx;Password=xxxxxxxxxx;Encrypt=yes;" />

...I end up with a connection string, like this:

Driver={"{Cisco Information Server 7.0}}"};server=xxxxxxxxxx;port=xxxxxxxxxx;domain=xxxxxxxxxx;datasource=xxxxxxxxxx;database=xxxxxxxxxx;user=xxxxxxxxxx;encrypt=yes

If I remove the curly braces and use a connection string like this:

<OdbcConnection Name="CIS" ConnectionString="Driver=Cisco Information Server 7.0;Server=xxxxxxxxxx;Port=xxxxxxxxxx;Domain=xxxxxxxxxx;dataSource=xxxxxxxxxx;database=src;User=xxxxxxxxxx;Password=xxxxxxxxxx;Encrypt=yes;" />

...I still end up with a connection string like this:

Driver={"Cisco Information Server 7.0"};server=xxxxxxxxxx;port=xxxxxxxxxx;domain=xxxxxxxxxx;datasource=xxxxxxxxxx;database=xxxxxxxxxx;user=xxxxxxxxxx;encrypt=yes

Edit Just for clarity (as it was missing from the original post) the extra quotes added by the BIML compiler mean the driver is unrecognised and executing the generated package throws this error:

Execute SQL Task: Failed to acquire connection "CIS". Connection may not be configured correctly or you may not have the right permissions on this connection.

I have a working solution (more of a workaround) by creating a duplicate registry entry for the driver in HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI, with the non-alphanumeric chars removed, then referencing this in BIML.

However, I'd like to know if there's a way to stop the BIML compiler wrapping quotes round my ODBC driver.

Thanks in advance for any suggestions!

BIML code

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OdbcConnection Name="CIS" ConnectionString="Driver={Cisco Information Server 7.0};Server=xxxxxxxxxx;Port=xxxxxxxxxx;Domain=xxxxxxxxxx;dataSource=xxxxxxxxxx;database=src;User=xxxxxxxxxx;Password=xxxxxxxxxx;Encrypt=yes;" />
    </Connections>

    <Packages>
        <Package Name="TestPackage" ConstraintMode="Parallel" ProtectionLevel="EncryptSensitiveWithUserKey" DelayValidation="true">

            <Variables>
                <Variable Name="TestResult" DataType="Int32">0</Variable>
            </Variables>

            <Tasks>
                <ExecuteSQL Name="ConnectionTest" ConnectionName="CIS" ResultSet="SingleRow">
                    <DirectInput>SELECT COUNT(*) FROM SCHEMA.TABLE</DirectInput>
                    <Results>
                        <Result Name="1" VariableName="User.TestResult" />
                        <!-- ODBC requires a 1-based param number -->
                    </Results>
                </ExecuteSQL>
            </Tasks>
        </Package>
    </Packages>
</Biml>

score 1 · Answer 1 · edited May 23 '17 at 12:00

1

Does ConnectionString="Driver={{{Cisco Information Server 7.0}}};... fix the problem?

I ran into a different issue in Biml where braces were being expanded as formatters and the solution is to double up the braces in .net at least

edited May 23 '17 at 12:00

Community

1
1

answered Mar 09 '17 at 18:46

billinkc

59,250
9
102
159

Thanks, @billinkc, will try tomorrow morning (UK) time and let you know via way of a comment, or an accepted answer. – TechSpud Mar 09 '17 at 21:43
No joy, @billinkc. BIML input `Driver={{{Cisco Information Server 7.0}}}` gives a connection string `Driver={"{{{Cisco Information Server 7.0}}}}}}"}` – TechSpud Mar 10 '17 at 07:16

score 1 · Accepted Answer · answered Mar 10 '17 at 08:26

I have another workaround (instead of creating an alphanumeric-only copy of the ODBC registry entry).

@billinkc's answer, unfortunately, didn't help, as this related to escaping curly braces within a C# string.

The workaround is to create an ADO.NET ODBC connection, like this:

<AdoNetConnection Name="CISADO" Provider="System.Data.Odbc.OdbcConnection, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" ConnectionString="Driver=Cisco Information Server 7.0;Server=xxxxxxxxxx;Port=xxxxxxxxxx;Domain=xxxxxxxxxx;dataSource=xxxxxxxxxx;database=xxxxxxxxxx;User=xxxxxxxxxx;Password=xxxxxxxxxx;Encrypt=yes;" />

For some reason, the BIML compiler 'likes' this connection string and doesn't try to wrap it in quotes and/or curly braces.

I'll leave the question open, as I'd really like someone to give an answer and solution to prevent the unwanted quotes & curly braces being added by the BIML compiler. This answer will hopefully be useful to someone (and me!) in the future.

How can I prevent BIML Express quoting an ODBC Driver

2 Answers2