在数据导入期间，如何将平面文件中的 dd-mmm-yy 值格式化为smalldatetime？
我有一个平面文件，通过现有的 SSIS 包导入到 SQL Server 中。我需要对包进行更改以适应平面文件中的新字段。新字段是日期字段，格式为 dd-mmm-yy（例如 25-AUG-11）。平面文件中的日期字段将为空（例如空格/空白）或填充日期。我无法控制平面文件中的日期格式。
我需要将平面文件中的日期字段导入到现有的 SQL Server 表中，目标字段数据类型为smalldatetime。
我建议将日期作为字符串导入到加载表中，然后在从加载表中获取数据时转换为smalldatetime。但是是否有另一种可能的方法来解析日期格式 dd-mmm-yy 以将其直接加载到smalldatetime 字段中，而无需使用从加载表转换为smalldatetime。我不太清楚如何解析日期格式，特别是月份。欢迎任何建议。
I have a flat file which is imported into SQL Server via an existing SSIS package. I need to make a change to the package to accommodate a new field in the flat file. The new field is a date field which is in the format dd-mmm-yy (e.g. 25-AUG-11). The date field in the flat file will either be empty (e.g. a space/whitespace) or populated with a date. I don’t have any control over the date format in the flat file.
I need to import the date field in the flat file into an existing SQL Server table and the target field data type is smalldatetime.
I was proposing to import the date as a string into a load table and then convert to smalldatetime when taking the data from the load table. But is there another possible way to parse the date format dd-mmm-yy to load this straight into a smalldatetime field without having to use convert to smalldatetime from the load table. I can’t quite think how to parse the date format, particularly the month. Any suggestions welcome.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这是一个示例，可以让您了解可以做什么。理想情况下，在 SSIS 包或任何 ETL 作业中，您应该考虑到数据可能并不完全符合您的要求。您需要采取适当的步骤来处理可能时不时弹出的不正确或无效数据。这就是为什么 SSIS 在数据流任务中提供了大量转换任务，您可以利用它们来清理数据。
根据您的情况，您可以利用
派生列转换或数据转换转换来实现您的要求。该示例是在
SSIS 2008 R2中创建的。它展示了如何读取包含日期的平面文件并将其加载到 SQL 表中。我创建了一个简单的 SQL 表来导入平面文件数据。
在 SSIS 包上，我有一个 SQL 连接管理器和一个平面文件连接管理器。平面文件连接的配置如下所示。
在 SSIS 包上，我在“控制流”选项卡上放置了一个“数据流任务”。在数据流任务内部，我有一个平面文件源、派生列转换和一个 OLE DB 目标。由于平面文件源和 OLE DB 目标很简单，因此我将把它们留在这里。派生转换使用表达式
(DT_DBDATE)SmallDate创建一个新列。请注意，您还可以使用数据转换来执行相同的操作。这个新列 SmallDateTimeValue 应映射到 OLE DB 目标中的数据库列。如果执行此包，它将失败，因为并非文件中的所有值都有效。
在您的情况下失败的原因是因为无效数据直接插入到表中。在您的情况下，该表将引发异常，导致包失败。在此示例中，包失败，因为派生列转换的默认设置是在出现任何错误时使组件失败。因此，让我们放置一个虚拟转换来重定向错误行。我们将为此目的进行Multicast改造。它不会真正做任何事情。理想情况下，您应该使用 OLE DB 目标或您选择的其他目标组件将错误行重定向到另一个表，以便您可以分析导致错误的数据。
从派生转换中拖动红色箭头并将其连接到多播转换。这将弹出“配置错误输出”对话框。将错误和截断列下的值从失败组件更改为
重定向行。这会将任何错误行重定向到多播转换，并且不会进入表中。现在，如果我们执行该包，它将成功运行。请注意每个方向显示的行数。
这是进入表中的数据。只有 2 行有效。您可以查看显示文件中数据的第一个屏幕截图，您可以看到只有 2 行有效。
希望这能让您了解在 SSIS 包中实现您的要求。
Here is an example that might give you an idea of what you can do. Ideally, in an SSIS package or in any ETL job, you should take into account that data may not be exactly what you would like it to be. You need to take appropriate steps to handle the incorrect or invalid data that might pop up now and then. That's why SSIS comes up with lots of Transformation tasks within Data Flow Task which you can make use of to clean up the data.
In your case, you can make use of
Derived Columntransformation orData conversiontransformation to achieve your requirements.The example was created in
SSIS 2008 R2. It shows how to read a flat file containing the dates and load into an SQL table.I created a simple SQL table to import the flat file data.
On the SSIS package, I have a connection manager to SQL and one for Flat file. Flat file connection is configured as shown below.
On the SSIS package, I placed a Data Flow Task on the Control Flow tab. Inside, the Data Flow task, I have a Flat File Source, Derived Column transformation and an OLE DB Destination. Since the Flat file source and OLE DB destination are straightforward, I will leave those out here. The Derived transformation creates a new column with the expression
(DT_DBDATE)SmallDate. Note that you can also use Data Conversion transformation to do the same. This new column SmallDateTimeValue should be mapped to the database column in OLE DB Destination.If you execute this package, it will fail because not all the values in the file are valid.
The reason why it fails in your case is because the invalid data is directly inserted into the table. In your case, the table will throw an exception making the package to fail. In this example, the package fails because the default setting on the Derived column transformation is to fail the component if there is any error. So, let's place a dummy transformation to redirect the error rows. We will Multicast transformation for this purpose. It won't really do anything. Ideally, you should redirect the error rows to another table using OLE DB Destination or other Destination component of your choice so you can analyze the data that causes the errors.
Drag the red arrow from Derived transformation and connect it to the Multicast transformation. This will popup the Configure Error Output dialog. Change the values under the column Error and Truncation from Fail component to
Redirect row. This will redirect any error rows to the Multicast transformation and will not get into the tables.Now, if we execute the package, it will run successfully. Note the number of rows displayed in each direction.
Here is the data that got into the table. Only 2 rows were valid. You can look at the first screenshot that showed the data in the file and you can see only 2 rows were valid.
Hope that gives you an idea to implement your requirement in the SSIS package.
它应该按原样直接加载到 SMALLDATETIME 字段中。请记住，日期在 SQL Server 中只是数字，以所需的日期/时间格式呈现给用户。 SSIS 包应该将 25-AUG-2011 读取为日期数据类型，并将其插入 SMALLDATETIME 字段，不会出现任何问题。
包是否抛出错误或其他什么？
It should load straight into a SMALLDATETIME field as it is. Remember, dates are just numbers in SQL Server, which are presented to the user in the desired date/time format. The SSIS package should read 25-AUG-2011 just fine as a date data type, and insert it into a SMALLDATETIME field without issues.
Was the package throwing an error or something?