BIML中Dataflow任务无法读取配置文件，Execute SQL Task正常

Let's break down why your Dataflow Task is throwing login errors while your Execute SQL Task runs without issues—this is a classic SSIS quirk when dealing with runtime credential configurations, especially for Azure SQL targets.

The Core Issue #

Execute SQL Tasks and Dataflow Tasks handle connection manager properties differently:

• Execute SQL Tasks directly pull runtime values (like username/password from your config file) when they run, so they respect the configuration without hiccups.
• Dataflow Tasks (and their embedded OLE DB Source/Destination components) often validate connections at design time by default. This means they might cache old or incorrect credentials instead of pulling the updated values from your config file when the package runs.

Step-by-Step Fixes #

Here are the key adjustments to your BIML code and package setup to resolve this:

1. Enable DelayValidation on the Dataflow Task

This forces the Dataflow to wait until runtime to validate the connection, which ensures it uses the credentials loaded from your configuration file instead of design-time cached values.

2. Explicitly Map Credential Properties in the Connection Configuration

Make sure your Azure SQL connection manager in BIML explicitly maps the UserName and Password properties to your config file—don't rely on just the connection string. Dataflow components need these properties to be explicitly resolved at runtime.

3. Verify Azure SQL Login Details

Double-check that your config file's username includes the Azure SQL server suffix (e.g., yourlogin@yourazuresqlserver.database.windows.net) if you're using SQL authentication. Azure SQL requires this full username format for logins.

Modified BIML Code Examples #

Let's adjust your packages to implement these fixes:

Working Staging Creation Package (Reference)

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package Name="Create_Staging" ConstraintMode="Linear">
            <Connections>
                <OleDbConnection Name="AzureSQL_Target" ConnectionString="Data Source=your-azure-sql.database.windows.net;Initial Catalog=TargetDB;Provider=SQLNCLI11.0;" />
                <!-- Explicitly map credential properties to config file -->
                <Configurations>
                    <Configuration Name="AzureSQL_Credentials" ConfigurationType="File" Format="Xml">
                        <ConfiguredProperties>
                            <ConfiguredProperty PropertyName="UserName" Path="\Package.Connections[AzureSQL_Target].Properties[UserName]" />
                            <ConfiguredProperty PropertyName="Password" Path="\Package.Connections[AzureSQL_Target].Properties[Password]" />
                        </ConfiguredProperties>
                    </Configuration>
                </Configurations>
            </Connections>
            <Tasks>
                <ExecuteSQLTask Name="Create Staging Tables" ConnectionName="AzureSQL_Target">
                    <DirectInput>
                        CREATE TABLE Staging.Customers (Id INT, Name VARCHAR(100));
                    </DirectInput>
                </ExecuteSQLTask>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Fixed Staging Load Package with Dataflow Task

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Packages>
        <Package Name="Load_Staging" ConstraintMode="Linear">
            <Connections>
                <OleDbConnection Name="LocalSQL_Source" ConnectionString="Data Source=localhost;Initial Catalog=SourceDB;Provider=SQLNCLI11.0;Integrated Security=SSPI;" />
                <OleDbConnection Name="AzureSQL_Target" ConnectionString="Data Source=your-azure-sql.database.windows.net;Initial Catalog=TargetDB;Provider=SQLNCLI11.0;" />
                <!-- Reuse the same credential configuration as the working package -->
                <Configurations>
                    <Configuration Name="AzureSQL_Credentials" ConfigurationType="File" Format="Xml">
                        <ConfiguredProperties>
                            <ConfiguredProperty PropertyName="UserName" Path="\Package.Connections[AzureSQL_Target].Properties[UserName]" />
                            <ConfiguredProperty PropertyName="Password" Path="\Package.Connections[AzureSQL_Target].Properties[Password]" />
                        </ConfiguredProperties>
                    </Configuration>
                </Configurations>
            </Connections>
            <Tasks>
                <!-- Enable DelayValidation to skip design-time connection validation -->
                <DataflowTask Name="Load Customers to Staging" DelayValidation="true">
                    <Transformations>
                        <OleDbSource Name="Source Customers" ConnectionName="LocalSQL_Source">
                            <DirectInput>SELECT Id, Name FROM dbo.Customers;</DirectInput>
                        </OleDbSource>
                        <OleDbDestination Name="Destination Staging Customers" ConnectionName="AzureSQL_Target">
                            <ExternalTableOutput Table="[Staging].[Customers]" />
                        </OleDbDestination>
                    </Transformations>
                </DataflowTask>
            </Tasks>
        </Package>
    </Packages>
</Biml>

Extra Troubleshooting Tips #

• If you're using Azure AD authentication, switch to the MSOLEDBSQL provider instead of SQLNCLI11.0—it has better support for modern Azure authentication methods.
• Confirm that your SQL login has db_datawriter permissions on the target Azure SQL database (the Execute SQL Task might have used a login with higher privileges, while the Dataflow uses the runtime credentials which might lack write access).
• Test running the package in debug mode and check the Connection Managers tab at runtime to verify that the correct username/password are being loaded from the config file.

内容的提问来源于stack exchange，提问作者Matt Lakin