SSIS：为​​什么我的文本分隔符不工作？ [英] SSIS: Why won't my Text Delimiter work?

问题描述

最初我写了以下内容：

环境：
-



涉及的文件：Destination Manager： code> OLEDB SQL Server 表

源管理器： FLATFILE - CSV 档案

  FORMAT： $ b HeadRowDelimiter：{CR} {LF} 
列分隔符：逗号{，} 
文本限定符：（手动设置）
要跳过的标题行：0 
列宽： 100 
列类型：DT_STR 
  

我的文件有以下列：

  Year，Lg ...，Div Finish，Playoffs，PF，PA ... OSRS，DSRS 
  

我只想检索：

  ，Lg ...，Div Finish，Playoffs 
  

但不是：

  | 2015，'NFL'，...，'2nd of 4'，NULL，...， -  4,0.3 
 | 2014 ，'NFL'，...，'3rd of 4'，NULL，...，0.6，-4.4 
  

$ b b

SSIS 包将失败，并显示类似如下的消息：

  [NFL_Team_List [52]]错误：数据转换失败。列季后赛的数据转换返回状态值4和状态
  

text文本已截断或一个或更多的字符在
目标代码页中没有匹配。

然而，看看经过几次的数据，I
注意以下事项：

  | 2015，'NFL'，...，'2nd of 4'，'339,345 ，-6，Quinn，Jones，Ryan ...'
 | 2014，'NFL'，...，'3rd of 4'，'299,422，-123，Reeves Phillips，Brooking ...'
  

所以问题出在我的分隔符不像分隔符！即使我设置了文本限定符设置，似乎 SSIS 无法在动态 FOREACHLOOP 中强制实施此规则，

b $ b

解决方案

您已将文本限定符设置为

Originally I had wrote the following:

Environment: - SSIS 2012, Microsoft Visual Studio

Files Involved: Destination Manager: OLEDB SQL Server Table

Source Manager: FLATFILE - CSV File

    FORMAT: Delimited
    HeadRowDelimiter: {CR}{LF}
    Column Delimiter: Comma {,}
    Text qualifier: " (manually set)
    Header rows to skip: 0
    Column Width: 100
    Column Type: DT_STR

My File has the following columns:

Year, Lg..., Div Finish, Playoffs, PF, PA...OSRS, DSRS

I wish to only retrieve:

Year, Lg..., Div Finish, Playoffs

But instead of:

|2015,'NFL',...,'2nd of 4',NULL,...,-4,0.3
|2014,'NFL',...,'3rd of 4',NULL,...,0.6,-4.4

The SSIS package would fail with a message similar to this:

[NFL_Team_List [52]] Error: Data conversion failed. The data conversion for column "Playoffs" returned status value 4 and status

text "Text was truncated or one or more characters had no match in the target code page.".

However, looking at the data that did go through a couple of times, I notice the following:

|2015,'NFL',...,'2nd of 4','339,345,-6,Quinn,Jones,Ryan...'
|2014,'NFL',...,'3rd of 4','299,422,-123,Reeves Phillips,Brooking...'

So the problem was on my delimiters are not acting like delimiters! Even though I set the Text Qualifier setting, it seems that SSIS is unable to actually enforce this rule in the dynamic FOREACHLOOP, leading to the issue.

QUESTIONS:

• Why is this the case?

• How can I get the Text Qualifier to work as designed?

解决方案

You've set your text qualifier to " but it is actually '

这篇关于SSIS：为​​什么我的文本分隔符不工作？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！