在 BIML 中的数据流之前创建表 [英] Create table before the dataflow in BIML

问题描述

我正在使用 BIML 和 BIDSHelper 创建 SSIS 包.我正在尝试将数据从 csv 导入 sql server.我想在数据流发生之前在目标数据库中创建表.这是我的代码:

I am using BIML and BIDSHelper to create SSIS package. I am trying to import data from csv to sql server. I want to create table in the destination database before the dataflow happens. Here is my code:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>       
    <OleDbConnection Name="CM_OLE" 
                     ConnectionString="Data Source=(localdb)\projects;Initial Catalog=test;Integrated Security=SSPI;Provider=SQLNCLI11">
    </OleDbConnection>
    <FlatFileConnection
            Name="FF Source"
            FileFormat="FFF Source"
            FilePath="F:\test.csv"
            CreateInProject="false" />
</Connections>
<FileFormats>
    <FlatFileFormat
            Name="FFF Source"
            CodePage="1252"
            RowDelimiter="CRLF"
            ColumnNamesInFirstDataRow="true"
            IsUnicode="false"
            FlatFileType="Delimited"
            TextQualifer="_x0022_"
            HeaderRowsToSkip="0">
        <Columns>               
            <Column Name="Column1" Length="50" InputLength="50" MaximumWidth="50" DataType="AnsiString"  ColumnType="Delimited"  CodePage="1252" Delimiter="," TextQualified="true" />
            <Column Name="Column2" Precision="10" Scale="2"  DataType="Decimal"  ColumnType="Delimited"  CodePage="1252" Delimiter="CRLF" TextQualified="true"  />
        </Columns>
    </FlatFileFormat>
</FileFormats>  
<Packages>      
    <Package ConstraintMode="Linear" Name="NumericParsingFromFlatFileInsertIdentity">
        <Tasks> 
            <ExecuteSQL Name="Create table sometablename" ConnectionName="CM_OLE">
                 <DirectInput>
                      CREATE TABLE sometablename(column1 varchar(50) NOT NULL, column2 varchar(10,2) NOT NULL);
                      GO 
                 </DirectInput>
            </ExecuteSQL>
            <Dataflow Name="DFT Source">
                <Transformations>
                    <FlatFileSource ConnectionName="FF Source" Name="FF Source" />
                    <OleDbDestination ConnectionName="CM_OLE" Name="OLEDB DST">
                        <ExternalTableOutput Table="sometablename"></ExternalTableOutput>
                    </OleDbDestination>                     
                </Transformations>
            </Dataflow>         
        </Tasks>
    </Package>
</Packages>

当我尝试生成包时，它说cannot execute query select * from sometablename invalid object name.我知道表 sometablename 不存在，所以它会抛出错误.那么，如何自动创建表?我已经阅读了 BI 思想和理论.第 2 部分展示了创建表的方法.我的理解是，最后它还会创建 ExecuteSQl 来创建表.我很困惑如何在数据流之前运行表创建脚本或其他 BIML 必须提供什么?

When I try to generate package it says cannot execute query select * from sometablename invalid object name. I understand that table sometablename doesnot exist so it throws the error. So, How could I create the table automatically? I have read the series BI Thoughts and Theories. Part 2 shows way to create table. My understanding is that at the end it also create ExecuteSQl to create the table. I am confused how to run table creation script before the dataflow or what other alternative BIML has to offer?

提前致谢

推荐答案

看来你是尝试使用 BIML 是不可能的.

SSIS 数据流要求所有外部列元数据在设计时间.没有办法解决这个问题，所以 Biml 编译器是需要查询数据源以获取此信息，即然后发射到包中.BIDS/SSDT 进行此验证在您工作时不断.Biml 仅在构建时执行.

SSIS dataflows require ALL external column metadata to be available at design time. There is no way around this, so the Biml compiler is required to query the data source to get this information, which is then emitted into the package. BIDS/SSDT does this validation constantly as you are working. Biml does it only at build time.

ValidateExternalMetadata=false 的目的其实是为了让 SSIS避免检查中定义的外部列数据流元数据仍然匹配外部数据源包运行时的验证阶段.但是在设计/建造时，我们仍然需要该元数据存在，以便我们可以创建外部首先是列元数据.需要明确的是，这对于本机 BIDS/SSDT 和 Biml.

The purpose of ValidateExternalMetadata=false is actually for SSIS to refrain from checking that the external columns defined in the dataflow metadata still match the external data source during the validation phase when the package is run. But at design/build time, we still need that metadata to exist so that we can create the external column metadata in the first place. To be clear, this is true both for native BIDS/SSDT and for Biml.

ValidateExternalMetadata 由 SSIS 团队为场景提供例如动态创建将匹配的表或文件预定的模式.通常你会预先构建架构您的开发环境(您针对它构建)然后动态地根据需要在生产中创建相同的模式.禁用验证意味着您可以将动态创建作为从动态创建的包中读取或加载到动态创建的包中对象.

ValidateExternalMetadata was provided by the SSIS team for scenarios such as dynamically creating tables or files that will match a predetermined schema. Usually you would have the schema prebuilt on your dev environment (which you build against) and then dynamically create the same schema on production as it's needed. Disabling validation means that you can do the dynamic creation as part of the same package that reads from or loads into those dynamically created objects.

我们确实认识到需要在没有模式在 Dev 中实现.我们正在关注的事情之一在未来的版本中做的是一个离线元数据"功能允许您使用 Biml 来声明您的数据流元数据，而无需在构建时检索它.会有一些脚本工作用户构建元数据以匹配其外观的部分就像在运行时一样，但如果他们做对了，像你这样的场景将被启用.

We do recognize that there's a need to do builds without having the schema materialized in Dev either. One of the things we're looking at doing in a future release is an "Offline Metadata" feature that would allow you to use Biml to declare your dataflow metadata without having to retrieve it at build time. There would be some scripting work on the user's part to construct the metadata to match what it will look like at run time, but if they get that right, scenarios like yours will be enabled.

您可以做的是将 ValidateExternalMetadata="false" 添加到您的 OLE DB 目标.在您的开发环境中手动创建表，然后生成包.

What you could do is add the ValidateExternalMetadata="false" to your OLE DB Destination. Create the table manually on your development environment and then generate the package.

它应该在任何其他环境中执行都没有问题，因为您将 ValidateExternalMetadata 设置为 false.

It should execute without problems on any other environment because you set ValidateExternalMetadata to false.

这篇关于在 BIML 中的数据流之前创建表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！