BIML生成SSIS包报错：DerivedColumn输出无法绑定至ADO.Net Destination如何解决？

First, let's break down the error you're encountering:

"EmitSsis. Internal Compiler Error: Workflow EmitSsis contains fatal errors. Phase execution halted. For assistance, please send Biml Compiler error messages to support@varigence.com. See other errors for more information. Exception type: NullReferenceException"

Your hunch is exactly right—this null reference error almost certainly stems from the ADO.Net Destination pointing to a non-existent output path. Let's look at the key mistake in your code:

In your AdoNetDestination definition, you've specified:

<InputPath OutputPathName="CompletionCertificate.Output"/>

But your DerivedColumns component is named Derived1, so its default output path is Derived1.Output. The path CompletionCertificate.Output refers directly to your source component's output (which is why removing the DerivedColumns transform fixes the issue—it skips the broken reference entirely).

Here's the correct fix: #

Update the InputPath in your AdoNetDestination to point to the DerivedColumns component's output instead. Here's the revised code block:

<AdoNetSource Name="CompletionCertificate Source" ConnectionName="Connection1">
<DirectInput>SELECT ...</DirectInput>
</AdoNetSource>
<DerivedColumns Name="Derived1">
<InputPath OutputPathName="CompletionCertificate Source.Output"/>
<Columns>
<Column Name="IsOpeningBalanceStr" SsisDataTypeOverride="DT_BOOL" DataType="String" Length="150">TRIM(IsOpeningBalanceStr) == "1"</Column>
</Columns>
</DerivedColumns>
<AdoNetDestination Name="CompletionCertificate Destination" ConnectionName="Database (ADO.Net)">
<!-- Corrected output path pointing to Derived1's transform output -->
<InputPath OutputPathName="Derived1.Output"/>
<ExternalTableOutput Table="Schema.Table"/>
<Columns>
<Column .../>
<Column .../>
<Column .../>
</Columns>
</AdoNetDestination>

Extra checks to avoid similar issues: #

• Stick to default output naming: Unless you define custom outputs, every transform's output path follows the pattern [ComponentName].Output.
• Validate input paths: You already have this right for the DerivedColumns component (CompletionCertificate Source.Output correctly pulls data from your source).
• Double-check your expression: The TRIM(IsOpeningBalanceStr) == "1" logic to cast to DT_BOOL works, but ensure the source IsOpeningBalanceStr field actually contains values like "1"/"0" to avoid runtime conversion errors.
• Confirm column mappings: Make sure the columns in your AdoNetDestination match the output columns from Derived1 (including the new boolean IsOpeningBalanceStr column) to prevent mapping failures.

With this change, the BIML compiler will correctly resolve the DerivedColumn's output path, and the null reference exception should be resolved.

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