BIML CustomSsisConnection Issue - XML triple nested quote

Question

I'm trying to submit this custom connection from BIML to SSIS 2014:

<CustomSsisConnection Name="Xtract DEV" CreateInProject="1" CreationName="XTRACT" ObjectData="&lt;XtractConnectionManager ConnectionString=&quot;USER=SERVICEUSER LANG=EN MSHOST=SAPSERVER.SERVICES.DOMAIN R3NAME=DEV GROUP="Development Server" PASSWD=SERVICEPW &quot; /&gt;"/>

The GROUP part is where it fails "Development Server" - double quotations included. If I do it without quotations then when I run the BIML file, my created SSIS package has GROUP = Development instead of Development Server which is wrong. Placing the double quotations fails and so does the XML escape " since it is already being used and the quotation marks are supposed to be within the two " tags.

In summary the problem is:

<CustomSsisConnection ... ObjectData="...... ConnectionString&quot;..GROUP="Development Server" ... &quot; ..." />

My question is how do I get this to work? I realize this is a triple nested quote and " doesn't do the trick. So simple question: What do I replace the two "s with?

Answer 1

To make the XML well-formed, all you need to do is write the " as "

Now, perhaps that will produce something that isn't well-formed according to some other (non-XML) syntax. For example, if an XML attribute is supposed to contain valid JSON, then you couldn't write

<x json="{&quot;connection&quot;:&quot;a=&quot;b&quot;&quot;}"/>

Because after XML unescaping, the attribute would be

{"connection":"a="b""}

Now the JSON rules say that nested quotes should be escaped with a backslash, so the JSON should be

{"connection":"a=\"b\""}

which means that the XML should be

<x json="{&quot;connection&quot;:&quot;a=\&quot;b\&quot;&quot;}"/>

Now in your case, without knowing something about "BMIL" or "SSIS", I don't know what the rules for the inner syntax might be: but the approach is the same: work out how the "triply nested" quotes should be escaped according to the rules of that inner syntax, and then escape the resulting string using the XML rules.

Answer 2

Just use ordinary space as the string is.. Look at my Initial Catalog, it has space also, and it works fine.

Example:

<Connection Name="TESTSERVER" ConnectionString="Data Source=.;Initial 
Catalog=test test;Provider=SQLNCLI11;Integrated Security=SSPI;Auto 
Translate=False;">