让 SSIS 检测 csv 文件的列大小 [英] Have SSIS detect the column sizes of a csv file

问题描述

我正在尝试使用 SSIS 将一个 csv 文件导入到 SQL 中，但遇到了一个基本缺陷.

I'm trying to import a csv file into SQL using SSIS and am hitting a fundamental flaw.

SSIS 似乎确定所有字段都是 varchar(50)，即使它正确识别了逗号分隔符.

SSIS seems to determine that all fields are varchar(50), even though it correctly identifies the comma delimiter.

当我尝试在 SQL 中将数据发送到我的表时，这会导致问题.

This is causing issues when I try to send the data to my table in SQL.

有没有办法让它认识到长度为 3 的字段实际上是长度为 3 的字段，而不是 50?

Is there a way of making it recognise that a field of length 3 is actually a field of length 3, and not 50?

谢谢

推荐答案

是的，平面文件连接管理器编辑器中有一个 Suggest Types 功能.

Yes, there's a Suggest Types function in the Flat File Connection Manager Editor.

假设您在第一张图片中显示了一个 CSV 文件.

Assume you have got a CSV file shown in the first image.

创建一个新的平面文件连接，并在您的计算机上浏览此文件.列"选项卡显示了文件示例.

Create a new Flat file connection, and browse this file on your computer. The Columns tab shows the sample of the file.

单击高级"选项卡.在那里您可以看到所有列都具有 DT_STR 类型，长度为 50.您可以看到 Suggest Types... 按钮.点击这里.

Click Advanced tab. There you can see all columns have DT_STR type with the length of 50. What you can see is the Suggest Types... button. Click this.

随意设置参数.在我的情况下，默认值没问题.点击确定.

Set parameters as you like. Defaults are all right in my case. Click OK.

现在第一列的类型为DT_STR，长度为1.(另外两列也有新的类型.数字列有DT_I1(因为我们选择了最小的合适整数类型选项)，日期列得到 DT_DATE.

Now the first column has the type of DT_STR with the length of 1. (The other two columns have got new types as well. The Number column got DT_I1 (because we choosed the smallest appropriate integer type option), and the Date column got DT_DATE.

这篇关于让 SSIS 检测 csv 文件的列大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！