Project Connection is not found for OleDbSource using Biml

Question

I am having an issue when I generate a package with biml which references a project level connection manager. The connection manager is not found for an OleDb Source, but works fine in a SQL task. Additionally, package level connection managers work fine.

Here is example code:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="sql" ConnectionString="data source=.;initial catalog=Staging;Provider=SQLNCLI11.1;Integrated Security=sspi;" />
    </Connections>
    <Projects>
        <PackageProject Name="Test">
            <Packages>
                <Package PackageName="MyPackage2"/>
            </Packages>
            <Connections>
                <Connection ConnectionName="sql"/>
            </Connections>
        </PackageProject>
    </Projects>
    <Packages>
        <Package Name="MyPackage2">
            <Tasks>
                <ExecuteSQL Name="TestSQL" ConnectionName="sql">
                    <DirectInput>
                        Select 1
                    </DirectInput>
                </ExecuteSQL>
                <Dataflow Name="My Dataflow">
                    <Transformations>
                        <OleDbSource Name="Read" ConnectionName ="sql">
                            <DirectInput>
                                Select 1 as Test;
                            </DirectInput>
                        </OleDbSource>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>

When generated the OleDbSource has the following error:

Validation error. My Dataflow Read [2]: The runtime connection manager with the ID "{6253281E-98BA-45C3-ACAB-BC58B34CC627}" cannot be found. Verify that the connection manager collection has a connection manager with that ID.

I have tried a number of different things which did not help:

Set CreateInProject to true for the connection manager
Used Connection as well as OleDbConnection
Added the Connections collection to the package and referenced the connection

What am I missing?

@ChrisAlbert this is odd. If I delete the connection manager from the project and regenerate, it's ok. But if I regenerate when it again, while the CM already exists, the CM is not found — Mark Wojciechowicz

Chris Albert Chris Albert · Accepted Answer · 2017-05-03T14:41:19

I copy an pasted your BIML and it generated all files without error. When I made a change and regenerated the files I received the same error message you did.

To resolve the issue I deleted the connection manager manually then regenerated the files. I am not sure what is causing this to happen but it this is a reliable work around to get the package working again.

Project Connection is not found for OleDbSource using Biml

1 Answers