Search code examples
odbcsql-server-data-toolsbiml

How can I prevent BIML Express quoting an ODBC Driver

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>
Solution

  • 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.