代码之家 › 专栏 › 技术社区 › starskythehutch Bennett Keller

How to remove control characters from DTS/SSIS imports?

dts ssis sql-server

0

starskythehutch Bennett Keller · 技术社区 · 16 年前

我工作的公司有很多系统,其中源数据来源于平面文件或Excel工作簿。然后使用DTS将这些文件导入到SQLServer数据库中。

Quite often these files contain various control characters (for instance I've just spent an hour finding that some records have a \0 in them which apparently SAP like to pad things with) which ideally we would like to strip out before they get into our database.

我没有使用过DTS/SSIS,但我认为应该已经有了一些东西,或者至少是一个消毒的最佳实践,我应该使用它。

Any ideas on how to do this in a repeatable fashion across many imports would be appreciated.

3 回复 | 直到 16 年前

1

Tim Stone 16 年前

I can only speak coming from my experiences with SSIS, so I apologise if this is only marginally useful.

In terms of general sanitation, I haven't come across any sort of purpose-build component for this job. I assume that's due to the notion that different scenarios can have widely different definitions of "bad data", but arguably things like stripping out non-printable characters might be a common business requirement so this is somewhat surprising.

当然,您可以总是依赖于派生列转换,或者在更复杂的情况下,脚本组件,但这些几乎不使逻辑在包中复制无痛。有些情况我们已经使用过 CozyRoc components to create scripts that we could share between packages, but in addition to having to actually have access to those components in the first place, it still involves a certain level of setup every time you need the functionality.

So, in cases like this, I personally feel like the best option is to consolidate your common sanitation routines into a Custom Data Flow Component . We developed one for ourselves to handle some very common data validation logic, and set the component editor up so that you could select checkboxes to enable whichever checks you wanted applied to a given column. Then it just becomes a matter of drag-and-drop and wiring up your data flow. At least for us, it was definitely worth taking the time to develop the component.

I don't know what DTS offers in this regard (I think it's fairly limited, or at least not friendly, but I could be wrong), so this is likely not useful for your legacy packages. I feel like Cade's solution would probably be most stress-free in that case, assuming it's feasible for your input data.

As far as general SSIS insight goes, I've found Jamie Thomson's blogs (以前) here ) very helpful, so you might want to check to see if he's discussed this particular topic before.

In any case, hopefully this was at least a little helpful, and good luck coming up with a solution.

侧记希望在你的数据被保存到你的数据库之前去清理你的数据。我们从一个不知道的供应商那里获取数据曾经 clean their data, then tries to shove it in XML to send it to us via a web service. Needless to say, the XML parser isn't too fond of non-printable characters.

2

0

Cade Roux 16 年前

如果某些字符总是可以简单地替换/删除,那么我们通常使用自定义C程序处理这个预导入。这显然不适用于二进制文件或任何具有可变长度的列长度指示器列或EBCDIC等的文件,但如果可以使用它,则无论导入工具如何,都可以应用它。

3

0

Tobiasopdenbrouw 16 年前

Depending on your needs, you could also do the following:

Import the data (through SSIS / DTS) into a 'staging' table in your DB
在数据库中有一个“清理”表,其中包含(每行)希望删除的任何字符串(或替换为其他字符串)。当然,对于不同的方案,这个表可以有额外的列
使用光标单步浏览表并进行清理。