SSIS 读取平面文件跳过第一行 [英] SSIS read flat file skip first row

问题描述

首先，我确实花了很多时间在研究上，我知道有很多相关的问题，但我找不到这个问题的正确答案.

First of all, I did spend quite some time on research, and I know there are many related questions, though I can't find the right answer on this question.

我正在创建一个 SSIS 包，它执行以下操作:1. 使用 HTTP 连接在本地下载并存储 CSV 文件.和 2. 读入 CSV 文件并存储在 SQL Server 上.

I'm creating a SSIS package, which does the following: 1. Download and store CSV file locally, using HTTP connection. And 2. Read in CSV file and store on SQL Server.

由于我的平面文件的结构，平面文件连接不断给我错误，无论是在 SSIS 中还是在 SQL 导入向导中.

Due to the structure of my flat file, the flat file connection keeps giving me errors, both in SSIS as in the SQL Import Wizard.

文件结构为:

"name of file"
"columnA","columnB"
"valueA1","valueB1"
"valueA2","valueB2"

因此行分母是行尾{CR}{LF}，列分母是逗号{,}，带有文本限定符.

Hence the row denominator is end of line {CR}{LF} and the column denominator is a comma{,}, with text qualifier ".

我只想导入值，而不是文件名或列名.

I want to import only the values, not the name of the file or the column names.

我尝试了设置并使用以下设置获得了正确的预览(见下图)

I played around with the settings and got the right preview with the following settings (see image below)

- Header rows to skip: 0
- Column names in the first data row: no
- 2 self-configured columns (string with columnWidth = 255)
- Data rows to skip: 2

当我运行 SSIS 包或 SQL 导入向导时，出现以下错误:

When I run the SSIS Package or SQL Import Wizard I get the following error:

[SSIS.Pipeline] 错误:SSIS 错误代码 DTS_E_PRIMEOUTPUFAILED.这平面文件源上的 PrimeOutput 方法返回错误代码 0xC0202091.当管道引擎调用时，组件返回失败代码总理输出().故障代码的含义由组件，但错误是致命的，管道停止执行.在此之前可能会发布错误消息并提供更多信息关于失败.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Flat File Source returned error code 0xC0202091. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

我不知道出了什么问题，我该怎么做才能使导入工作正常进行.

I can't figure out what goes wrong and what I can do to make this import work.

推荐答案

如果要跳过文件名和列名，需要将Header Rows to skip设置为2.你应该还要检查文件是否实际使用换行符 (LF) 而不是 CR+LF.在文本编辑器中检查换行符不足以检测差异，因为大多数编辑器都可以正确显示带有 CR+LF 或 LF 的文件.

If you want to skip the file name and the column names, you need to set Header Rows to skip to 2. You should also check whether the file actually uses line feeds (LF) instead of CR+LF. Checking the line breaks in a text editor isn't enough to detect the difference, as most editors display correctly files with both CR+LF or LF.

您可以通过单击平面文件源中的预览"按钮来检查您的设置结果.如果设置正确，您将看到数据正确对齐的网格.如果不是，你会得到一个错误，或者数据会以某种方式出错，例如非常多的列，第一个数据行中的列名等

You can check the results of your settings by clicking on the "Preview" button in your flat file source. If the settings are correct, you'll see a grid with your data properly aligned. If not, you'll get an error, or the data will be wrong in some way, eg a very large number of columns, column names in the first data row etc

这篇关于SSIS 读取平面文件跳过第一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！