如何在SSIS中设置数字列和日期时间的长度？
我确实有一个平面文件（csv 文件），它被提取并按其位置加载到另一个平面文件中。例如，我确实有 3 列，例如记录类型、员工编号和带有位置约束的日期。就像 Record 类型的长度为 2，数据类型为 alpha 一样，Emp number 的长度为 6，数据类型为 numeric，日期的长度类型为 26，格式为 yyyy-mm-dd.hh.mm.ss。 000000。
所以我在平面文件中的结果看起来像这样
016543092011-08-05.11.08.34.000000
026540392011-08-05.11.10.59.000000
我确实有两个问题
首先是当我提取csv文件时我无法将数字的长度设置为6，因为当我将Emp数字的数据类型指定为数字时我的长度被阻止，只允许我在平面文件源和数据转换中更改精度。结果是，在此字段旁边添加了更多空格，这在我的输出文件中是不需要的。
下一个是日期时间 -->一旦我使用精确的日期时间数据类型设置此列，我就无法设置该列的长度。
根据我当前的结果，我得到这样的输出（我无法避免中间的空白位置，因为我无法设置数字时间和日期时间的长度）
01654309 2011-08-05.11.08.34.000000
02654039 2011-08-05.11.10.59.000000
请建议我不同的方法来处理这个问题问题。我尝试在此过程之间使用临时表，但问题仍然存在。
I do have a flat file (csv file) which is extracted and loaded to another flat file by its position. For example I do have 3 columns like Record type, Emp number and date with a position constraint. Like Record type is of length 2 with a data type of alpha , Emp number is of length 6 with a data type of numeric and date with a length type of 26 in the format of yyyy-mm-dd.hh.mm.ss.000000.
So my result in the flat file would look like this
016543092011-08-05.11.08.34.000000
026540392011-08-05.11.10.59.000000
I do have two issues
First is I am not able to set the length of the numeric to 6 when I extract the csv file as when I specify the data type of Emp number as numeric then my length is blocked and only the precision is allowed for me to change in Flat file source and also in the Data conversion too. And the result is that there is more spaces added next to this field which is not needed in my output file
Next one is with the datetime --> i am not able to set the length for this one once i set this column with a data type of date time with precision.
With my current result I get the out put like this (I am not able to avoid the blank place which comes in between as I am not able to set the length for both numeric and also date time)
01654309 2011-08-05.11.08.34.000000
02654039 2011-08-05.11.10.59.000000
Please suggest me different ways to handle this issue. I have tried using a staging table in between this process but the issue is still prevailing.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
SSIS 通过将非字符串数据类型隐式转换为字符串将非字符串数据类型导出到平面文件 - 因为平面文件只能存储字符串值。由于转换是隐式的，因此您无法控制该转换。您需要显式地转换为字符串，然后您可以将其置于您的控制之下，以便获得您想要的格式。
在目标之前添加派生列组件。在该派生列中，显式转换数字，例如，创建一个新列并使用 RIGHT("000000" + (DT_WSTR, 6)[EmpNumber], 6) 等表达式来获取前导零填充的字符串。在平面文件目标中，您必须映射这个新列而不是 EmpNumber。您还必须修改平面文件连接管理器以将数据类型更改为长度为 6 的字符串（否则您将收到验证错误）。
SSIS exports non-string data types to Flat Files by implicitly converting them to strings - because Flat Files can only store string values. Since the conversion is implicit, there isn't anywhere for you to control that conversion. You need to make the conversion to a string explicit, and then you can put it under your control so you get the format you want.
Add a Derived Column component prior to your Destination. In that Derived Column, explicitly convert your numbers, for example, create a new column and use an expression such as RIGHT("000000" + (DT_WSTR, 6)[EmpNumber], 6) to get a leading-zero filled string. In your Flat File Destination, you'll have to map this NEW column instead of EmpNumber. You'll also have to modify the Flat File Connection Manager to change the data type to a string of length 6 (otherwise you'll get validation errors).