Dear Andy,
I'm working through the BIML stairway series as SqlServerCentral and I think I've hit a wall.
I have several instances of SQL Server on my laptop, so I need to specify a specific instance. The first few lines of the code for step 4 read:
<#@ import namespace="System.Data" #>
<#@ import namespace="Varigence.Hadron.CoreLowerer.SchemaManagement" #>
<# var connection = SchemaManager.CreateConnectionNode("SchemaProvider", "Data Source=(local);Initial Catalog=SSISIncrementalLoad_Source;Provider=SQLNCLI11.1;Integrated Security=SSPI;"); #>
<# var tables = connection.GenerateTableNodes(); #>
However, I have to change the Data Source to
Data Source=.\sql2012
Note that this is usually valid in a connection string and I use it all the time. However, BIML complains:
c:\Users\Redirection\brittg2\Documents\Visual Studio 2012\Projects\BIML\BIML\GenerateStagingPackages.biml(3,87) : Error 0 : Unrecognized escape sequence.
I think that BIML sees the back-slash and things it's an escape sequence, or is that the XML parser?
Anyway, I replaced the single back - slash with a double. That got me further, but I still have an error:
Validating BIML
1/4 Emitting Project IncrementalLoad_Source1.dtproj.
1/1 Emitting Package IncrementalLoad_Source1.
(-1,-1) : Error 0 : Node Source1 Source: Could not execute Query on Connection SSISIncrementalLoad_Source:
SELECT [ColID], [ColA], [ColB], [ColC] FROM [dbo].[Source1]
Instance failure.
FWIW, Building a package in the designer worked no problem. Made an OLEDB connector to SSISIncrementalLoad_Source. Added an OLDEDB source adapter to read the table [dbo].[Source1]. No problem!
Oh also, I ran Profiler to try see what was happening. As far as I can see, the query mentioned in the error:
SELECT [ColID], [ColA], [ColB], [ColC] FROM [dbo].[Source1]
Was never sent to the server. Is this a problem in the error reporting?
How do I get past the "Instance Failure" error?
Edit: I solved the failure, the problem is that the backslash must be escaped in C# but NOT in XML
Gerald Britton, Pluralsight courses