文章/答案/技术大牛

发布

社区首页 >问答首页 >使用ScriptComponent的BIML生成包，但不会在SSDT中打开

问使用ScriptComponent的BIML生成包，但不会在SSDT中打开
EN

Stack Overflow用户

提问于 2019-04-30 21:53:26

回答 1查看 729关注 0票数 2

更新:修改post，以包含最简单的解决方案，以重现问题。

我很难在BIML中使用ScriptComponentProject。我正在使用VS 2017和BIML Express 2018最新版本。

我正在尝试创建一个脚本组件，它将使用此博客帖子中列出的策略检索错误描述和违规列名。

针对脚本组件的BIML示例不多，但我的解决方案是以下方差多和Joost的博客的混合。

当我编译BIML时，它会创建没有错误的包；但是，当我打开包时，会收到错误消息：

“价值不低于预期范围”

下面是代码的简化版本，这是再现错误的最小值。应该只需将代码复制到.biml文件并生成SSIS包即可。示例从一个列CSV文件中读取，将源中的列转换为整数。将数据转换错误定向到脚本组件，然后脚本组件将违规列和错误描述列添加到输出缓冲区，然后将此输出写入平面文件目标。

任何帮助都将不胜感激。

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <FlatFileConnection Name="Errors" FilePath="C:\Users\username\Desktop\BasicSSISErrors.csv" FileFormat="Errors" />
        <FlatFileConnection Name="Source" FilePath="C:\Users\username\Desktop\BasicSSIS.csv" FileFormat="Source" />
    </Connections>
    <Packages>
        <Package Name="PackageFromBIML" Language="None" ConstraintMode="LinearOnCompletion" ProtectionLevel="EncryptSensitiveWithUserKey">
            <Tasks>
                <Dataflow Name="Data Flow Task">
                    <Transformations>
                        <FlatFileSource Name="Flat File Source" LocaleId="None" FileNameColumnName="" ConnectionName="Source" />
                        <DataConversion Name="Data Conversion">
                            <DataflowOverrides>
                                <OutputPath OutputPathName="Output">
                                    <Columns>
                                        <Column ErrorRowDisposition="RedirectRow" TruncationRowDisposition="RedirectRow" ColumnName="Converted" />
                                    </Columns>
                                </OutputPath>
                            </DataflowOverrides>
                            <Columns>
                                <Column SourceColumn="Integers" TargetColumn="Converted" DataType="SByte" />
                            </Columns>
                        </DataConversion>
                        <ScriptComponentTransformation Name="Script Component" ProjectCoreName="SC_a8f35334a2234702a5f92abdcbedb4e4">
                            <InputPath OutputPathName="Data Conversion.Error" />
                            <ScriptComponentProjectReference ScriptComponentProjectName="SC_a8f35334a2234702a5f92abdcbedb4e4" />
                        </ScriptComponentTransformation>
                        <FlatFileDestination Name="Flat File Destination" LocaleId="None" ConnectionName="Errors">
                            <Header></Header>
                        </FlatFileDestination>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
    <FileFormats>
        <FlatFileFormat Name="Errors" CodePage="1252" TextQualifier="_x003C_none_x003E_" ColumnNamesInFirstDataRow="true" RowDelimiter="">
            <Columns>
                <Column Name="Integers" Length="50" DataType="AnsiString" Delimiter="Comma" MaximumWidth="50" />
                <Column Name="ErrorCode" Delimiter="Comma" />
                <Column Name="ErrorColumn" Delimiter="Comma" />
                <Column Name="ErrorColumnName" DataType="AnsiString" Delimiter="Comma" />
                <Column Name="ErrorDescription" DataType="AnsiString" Delimiter="CRLF" />
            </Columns>
        </FlatFileFormat>
        <FlatFileFormat Name="Source" CodePage="1252" TextQualifier="_x003C_none_x003E_" ColumnNamesInFirstDataRow="true" RowDelimiter="">
            <Columns>
                <Column Name="Integers" Length="50" DataType="AnsiString" Delimiter="CRLF" MaximumWidth="50" />
            </Columns>
        </FlatFileFormat>
    </FileFormats>
    <ScriptProjects>
        <ScriptComponentProject Name="SC_a8f35334a2234702a5f92abdcbedb4e4" ProjectCoreName="SC_a8f35334a2234702a5f92abdcbedb4e4.csproj">
            <OutputBuffers>
                <OutputBuffer Name="Output 0">
                    <Columns>
                        <Column Name="ErrorColumnName" Length="500" DataType="AnsiString" CodePage="1252" />
                        <Column Name="ErrorDescription" Length="500" DataType="AnsiString" CodePage="1252" />
                    </Columns>
                </OutputBuffer>
            </OutputBuffers>
            <InputBuffer Name="Input 0">
                <Columns>
                    <Column Name="ErrorCode" />
                    <Column Name="ErrorColumn" />
                </Columns>
            </InputBuffer>
            <AssemblyReferences>
                   <AssemblyReference AssemblyPath="Microsoft.SqlServer.DTSPipelineWrap.dll" />
                   <AssemblyReference AssemblyPath="Microsoft.SqlServer.DTSRuntimeWrap.dll" />
                   <AssemblyReference AssemblyPath="Microsoft.SqlServer.PipelineHost.dll" />
                   <AssemblyReference AssemblyPath="Microsoft.SqlServer.TxScript.dll" />
                   <AssemblyReference AssemblyPath="System.dll" />
                   <AssemblyReference AssemblyPath="System.AddIn.dll" />
                   <AssemblyReference AssemblyPath="System.Data.dll" />
                   <AssemblyReference AssemblyPath="System.Xml.dll" />
            </AssemblyReferences>
                        <Files>
       <!-- Left alignment of .Net script to get a neat layout in package-->
            <File Path="AssemblyInfo.cs">
using System.Reflection;
using System.Runtime.CompilerServices;
[assembly: AssemblyTitle("SC_a8f35334a2234702a5f92abdcbedb4e4.csproj")]
[assembly: AssemblyDescription("")]
[assembly: AssemblyConfiguration("")]
[assembly: AssemblyCompany("")]
[assembly: AssemblyProduct("SC_a8f35334a2234702a5f92abdcbedb4e4.csproj")]
[assembly: AssemblyCopyright("")]
[assembly: AssemblyTrademark("")]
[assembly: AssemblyCulture("")]

[assembly: AssemblyVersion("1.0.*")]
                           </File>

                           <File Path="main.cs">
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public partial class ScriptMain : UserComponent
{
  public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);
        IDTSComponentMetaData130 componentMetaData = this.ComponentMetaData as IDTSComponentMetaData130;
        Row.ErrorColumnName = componentMetaData.GetIdentificationStringByID(Row.ErrorColumn);
    }
}
                </File>
            </Files>
        </ScriptComponentProject>
    </ScriptProjects>
</Biml>

biml

回答 1

Stack Overflow用户

回答已采纳

发布于 2019-05-03 16:23:34

我已经通过将SSIS项目属性切换到目标Server 2016来解决这个问题，它默认为以Server 2017为目标。

这似乎是方差产品中的一个bug，因为这个论坛帖子证实了其他人正在经历这个问题。除了2019年2月的“给我们一个回购”之外，没有其他任何更新。方差声明有一个“预览构建”来解决这个问题，但是预览构建没有链接到并跟踪线程中的帖子，请求预览生成的帖子没有得到回答。我猜是这是预览生成。。我尝试了2019年的预览，但它没有解决这个问题。Scott在文章中提到了预览构建修复不符合我的情况，他说，问题发生在底层平台目标，即使用VS 2017来瞄准SQL 2016。我没有这么做，而是使用VS2017来瞄准SQL 2017。

在此之后，我还遇到了另一个问题，我收到了错误：

无法创建用户组件，请确保至少有一个用SsisScriptTaskEntryPointattribute标记的类

我通过向Microsoft.SqlServer.ScriptTask.dll添加程序集引用来纠正这一点。此程序集引用不包含在“方差”示例中。

完全修正的解决办法如下：

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <FlatFileConnection Name="Errors" FilePath="C:\Users\username\Desktop\BasicSSISErrors.csv" FileFormat="Errors" />
        <FlatFileConnection Name="Source" FilePath="C:\Users\username\Desktop\BasicSSIS.csv" FileFormat="Source" />
    </Connections>
    <ScriptProjects>
        <ScriptComponentProject ScriptLanguage="CSharp"  Name="SCR - ErrorHelper" ProjectCoreName="SC_a8f35334a2234702a5f92abdcbedb4e4">
            <AssemblyReferences>
                   <AssemblyReference AssemblyPath="Microsoft.SqlServer.DTSPipelineWrap.dll" />
                   <AssemblyReference AssemblyPath="Microsoft.SqlServer.DTSRuntimeWrap.dll" />
                   <AssemblyReference AssemblyPath="Microsoft.SqlServer.PipelineHost.dll" />
                    <AssemblyReference AssemblyPath="Microsoft.SqlServer.ScriptTask.dll" />
                   <AssemblyReference AssemblyPath="Microsoft.SqlServer.TxScript.dll" />
                   <AssemblyReference AssemblyPath="System.dll" />
                    <AssemblyReference AssemblyPath="System.Data.dll" />
                    <AssemblyReference AssemblyPath="System.Xml.dll" />
                    <AssemblyReference AssemblyPath="System.Core.dll" />
                   <AssemblyReference AssemblyPath="System.AddIn.dll" />
                   <AssemblyReference AssemblyPath="System.Data.dll" />
                   <AssemblyReference AssemblyPath="System.Xml.dll" />
            </AssemblyReferences>
                        <Files>
       <!-- Left alignment of .Net script to get a neat layout in package-->
            <File Path="AssemblyInfo.cs">
using System.Reflection;
using System.Runtime.CompilerServices;

[assembly: AssemblyTitle("SC_2bca370105ff4883a705860bac68cfba.csproj")]
[assembly: AssemblyDescription("")]
[assembly: AssemblyConfiguration("")]
[assembly: AssemblyCompany("Varigence")]
[assembly: AssemblyProduct("SC_2bca370105ff4883a705860bac68cfba.csproj")]
[assembly: AssemblyCopyright("Copyright @ Varigence 2011")]
[assembly: AssemblyTrademark("")]
[assembly: AssemblyCulture("")]


[assembly: AssemblyVersion("1.0.*")]</File>
        <File Path="main.cs">
using System;
using System.Data;
using System.Linq;
using System.Net;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
  public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        Row.ErrorDescription = this.ComponentMetaData.GetErrorDescription(Row.ErrorCode);

        var componentMetaData130 = this.ComponentMetaData as IDTSComponentMetaData130;
        if (componentMetaData130 != null)
        {
            // 0 means no specific column is identified by ErrorColumn, this time.
            if (Row.ErrorColumn == 0)
            {
                Row.ErrorColumnName = "Check the row for a violation of a foreign key constraint. No specific column is identified by ErrorColum";
            }
            else
            {
                Row.ErrorColumnName = componentMetaData130.GetIdentificationStringByID(Row.ErrorColumn);
            }
        }
    }
}
                </File>
            </Files>
        <OutputBuffers>
                <OutputBuffer Name="Output0">
                    <Columns>
                        <Column Name="ErrorColumnName" Length="500" DataType="AnsiString" CodePage="1252" />
                        <Column Name="ErrorDescription" Length="500" DataType="AnsiString" CodePage="1252" />
                    </Columns>
                </OutputBuffer>
            </OutputBuffers>
            <InputBuffer Name="Input0">
                <Columns>
                    <Column Name="ErrorCode" />
                    <Column Name="ErrorColumn" />
                </Columns>
            </InputBuffer>
        </ScriptComponentProject>
    </ScriptProjects>
    <Packages>
        <Package Name="PackageFromBIML" Language="None" ConstraintMode="LinearOnCompletion" ProtectionLevel="EncryptSensitiveWithUserKey">
            <Tasks>
                <Dataflow Name="Data Flow Task">
                    <Transformations>
                        <FlatFileSource Name="Flat File Source" LocaleId="None" FileNameColumnName="" ConnectionName="Source" />
                        <DataConversion Name="Data Conversion">
                            <DataflowOverrides>
                                <OutputPath OutputPathName="Output">
                                    <Columns>
                                        <Column ErrorRowDisposition="RedirectRow" TruncationRowDisposition="RedirectRow" ColumnName="Converted" />
                                    </Columns>
                                </OutputPath>
                            </DataflowOverrides>
                            <Columns>
                                <Column SourceColumn="Integers" TargetColumn="Converted" DataType="SByte" />
                            </Columns>
                        </DataConversion>
                        <ScriptComponentTransformation Name="Script Component" ProjectCoreName="SC_a8f35334a2234702a5f92abdcbedb4e4">
                            <InputPath OutputPathName="Data Conversion.Error" />
                            <ScriptComponentProjectReference ScriptComponentProjectName="SCR - ErrorHelper" />
                        </ScriptComponentTransformation>
                        <FlatFileDestination Name="Flat File Destination" LocaleId="None" ConnectionName="Errors">
                            <Header></Header>
                        </FlatFileDestination>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
    <FileFormats>
        <FlatFileFormat Name="Errors" CodePage="1252" TextQualifier="_x003C_none_x003E_" ColumnNamesInFirstDataRow="true" RowDelimiter="">
            <Columns>
                <Column Name="Integers" Length="50" DataType="AnsiString" Delimiter="Comma" MaximumWidth="50" />
                <Column Name="ErrorCode" Delimiter="Comma" />
                <Column Name="ErrorColumn" Delimiter="Comma" />
                <Column Name="ErrorColumnName" DataType="AnsiString" Delimiter="Comma" />
                <Column Name="ErrorDescription" DataType="AnsiString" Delimiter="CRLF" />
            </Columns>
        </FlatFileFormat>
        <FlatFileFormat Name="Source" CodePage="1252" TextQualifier="_x003C_none_x003E_" ColumnNamesInFirstDataRow="true" RowDelimiter="">
            <Columns>
                <Column Name="Integers" Length="50" DataType="AnsiString" Delimiter="CRLF" MaximumWidth="50" />
            </Columns>
        </FlatFileFormat>
    </FileFormats>

</Biml>

票数 1

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/55929121

复制

相似问题

问使用ScriptComponent的BIML生成包，但不会在SSDT中打开
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用ScriptComponent的BIML生成包，但不会在SSDT中打开EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用ScriptComponent的BIML生成包，但不会在SSDT中打开
EN