如何在SSIS中管理巨大的固定宽度文件? [英] How to manage giant fixed-width file in SSIS?

问题描述

我有一个固定宽度的文件，大约 1200 个字符宽，大约有 300 多列.我正在寻找一种在 SSIS 中创建固定宽度数据源的方法，而不使用平面文件连接管理器的 UI.有没有办法修改列定义而不必使用 SSIS 中的 UI?我在项目的任何地方都找不到数据连接的文件.

I have a fixed width file that is about 1200 characters wide and has about 300+ columns. I'm looking for a way to create a fixed-width data source in SSIS without using the UI for the flat file connection manager. Is there a way to modify the column definitions without having to use the UI in SSIS? I can't find a file for the data connection anywhere in the project.

我是否注定要手动将 300 多列一一添加到平面文件连接管理器中?

Am I doomed to manually add 300+ columns into the flat-file connection manager one by one?

推荐答案

想到了两个选项.首先是安装 BIDSHelper 并使用 Create Fixed Width Columns

Two options come to mind. The first is to Install BIDSHelper and use the Create Fixed Width Columns

另一个，正如@ElectricLlama 提到的那样是使用 BIML.这也需要安装 BIDS Helper，但要将 .biml 文件转换为 .dtsx 简短演练

The other, as @ElectricLlama mentioned is to use BIML. This too will require the installation of BIDS Helper but to convert a .biml file into a .dtsx Short Walkthrough

这应该近似于创建一个带有平面文件连接管理器(带有单列)的包，添加一个数据流，并在其中使用我们的平面文件并将其连接到行计数.这是您想要的近似值.只需在 Columns 标记中填写 XML.

This should approximate creating a package with a flat file connection manager (with a single column) adding a data flow and inside that consume our flat file and wire it up to a Row count. This is approximate for what you want. Just fill in the XML in the Columns tag.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>
    <FlatFileConnection
        Name="FF dchess"
        FileFormat="FFF dchess"
        FilePath="C:\ssisdata\SO\Input\dchess.txt"
    />
</Connections>
<FileFormats>
    <FlatFileFormat
Name="FFF dchess"
CodePage="1252"
RowDelimiter="CRLF"
IsUnicode="false"
FlatFileType="RaggedRight"
        >
        <Columns>
            <Column Name="MyColumn" Length="08"  DataType="AnsiString"  ColumnType="FixedWidth"  CodePage="1252" />
        </Columns>
    </FlatFileFormat>
</FileFormats>
<Packages>
    <Package Name="dchess" ConstraintMode="Linear" ProtectionLevel="DontSaveSensitive">
        <Connections >
            <Connection ConnectionName="FF dchess" />
        </Connections>
        <Variables>
            <Variable Name="CurrentFileName" DataType="String">C:\ssisdata\so\Input\dchess.txt</Variable>
            <Variable Name="RowCountInsert" DataType="Int32">0</Variable>
        </Variables>
        <Tasks>
            <Dataflow Name="DFT Load file" >
                <Transformations>
                    <FlatFileSource
                        Name="FF_SRC dchess"
                        ConnectionName="FF dchess"
                        RetainNulls="true">
                    </FlatFileSource>
                    <RowCount Name="CNT Source" VariableName="User.RowCountInsert"></RowCount>
                </Transformations>
            </Dataflow>
        </Tasks>
    </Package>
</Packages>
</Biml>

生成的包看起来像

随意把你的下巴从地上捡起来;)

Feel free to pick your jaw up off the ground ;)

这篇关于如何在SSIS中管理巨大的固定宽度文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！