SSIS:平面文件默认长度

2021-12-30 00:00:00 etl sql-server ssis ssis-2016 sql-server-data-tools

我每天必须导入大约 50 个不同类型的文件.其中一些包含几列，有些包含多达 250 列.

I have to import about 50 different types of files every day. Some of them with a few columns, some inculde up to 250 columns.

平面文件连接始终将所有列默认为 50 个字符.有些列的长度可能超过 50 个字符，当然最终会出错.

The Flat File connection always defaults all columns to 50 chars. Some columns can be way longer than 50 chars, and will of course end up in errors.

目前我正在用记事本++进行愚蠢的搜索和替换 - 打开所有 SISS 包，替换:

Currently i am doing a stupid search&replace with notepad++ - Opening all SISS packages, replacing:

DTS:MaximumWidth="50"

由

DTS:MaximumWidth="500"

这是一个烦人的解决方法.是否有可能将 flatfile 字符串列的默认长度设置为某个值?

This is an annoying workaround. Is there any possibility to set a default length for flatfile string columns to a certain value?

我正在使用 Microsoft Visual Studio Professional 2015 和 SQL Server Data Tools 14.0.61021.0 进行开发

I am developing in Microsoft Visual Studio Professional 2015 and SQL Server Data Tools 14.0.61021.0

谢谢！

推荐答案

我认为没有办法通过 SQL Server Data Tools 实现这一点.

但是您可以采取一些解决方法来实现这一点:

But you can do some workaround to achieve this:

  1. 最简单的解决方案，在平面文件连接管理器 - 高级选项卡中，选择所有列(使用 Ctrl 键)并在一次编辑中更改它们的数据长度属性.(在@MikeHoney 回答中有详细说明)

  2. 您可以使用 BIML(商业智能标记语言)来创建 ssis 包，如果您刚接触 BIML，您可以访问 BIML Script 网站获取详细教程.

  1. Easiest solution, In the Flat file connection manager - Advanced Tab, select all columns (using Ctrl key) and change the data length property for them all in one edit. (detailed in @MikeHoney answer)

  2. You can use BIML (Business Intelligence Markup Language) to create ssis package, if you're new to BIML you can access to BIML Script website for detailed tutorials.

您可以创建一个小型应用程序，循环遍历文件夹中的 .dtsx 文件并将 DTS:MaximumWidth="50" 替换为 DTS:MaximumWidth="500" 使用普通的 String.Replace 函数或使用正则表达式.(你可以阅读我的回答@自动化版本号检索从 .Dtsx 文件查看使用正则表达式读取 .dtsx 文件的示例)

You can create a Small application that loop over .dtsx files in a folder and replace DTS:MaximumWidth="50" with DTS:MaximumWidth="500" using normal String.Replace function or using Regular expressions. (you can read my answer @ Automate Version number Retrieval from .Dtsx files to see an exmaple on reading .dtsx file using Regular expressions)

读取和替换 dtsx 文件内容的功能 (Vb.Net)

Public Sub FixDTSX(byval strFile as string)

    dim strContent as string = string.empty

    Using sr as new Io.StreamReader(strFile)

        strContent = sr.ReadToEnd()

        sr.Close()

    End Using

    strContent = strContent.Replace("DTS:MaximumWidth=""50""","DTS:MaximumWidth=""500""")

    Using sw as new Io.StreamWriter(strFile,False)

        sw.Write(strContent)

        sw.Close()

    End Using

End Sub

相关文章