SSIS:平面文件的默认长度 [英] SSIS: Flat File default length

问题描述

我每天必须导入大约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数据工具14.0.61021.0

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

谢谢！

推荐答案

我认为没有办法从SQL Server数据工具中实现这一目标.

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

But you can do some workaround to achieve this:

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

2. 您可以使用 BIML(商业智能标记语言)创建ssis程序包对于BIML来说是新手，您可以访问 BIML脚本网站以获取详细的教程.

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文件，并使用常规的String.Replace函数或正则表达式将DTS:MaximumWidth="50"替换为DTS:MaximumWidth="500". (您可以在自动获取版本号中阅读我的答案.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

这篇关于SSIS:平面文件的默认长度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！