如何在数据导入期间将平面文件中的dd-mmm-yy值格式化为smalldatetime？ [英] How do I format dd-mmm-yy values in flat file to smalldatetime during data import?

问题描述

我有一个平面文件，该文件通过现有的SSIS包导入到SQL Server中。我需要对包进行更改，以在平面文件中容纳一个新字段。新字段是日期字段，其格式为 dd-mmm-yy （例如 25-AUG-11 ）。平面文件中的日期字段将为空（例如空格/空白）或填充日期。我对平面文件中的日期格式没有任何控制。

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.

我需要将平面文件中的日期字段导入到现有的SQL Server表和目标字段数据类型为smalldatetime。

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.

我提议将日期作为字符串导入到加载表中，然后从加载表中获取数据时转换为smalldatetime。但是还有另一种可能的方法来解析日期格式dd-mmm-yy，以将其直接加载到smalldatetime字段中，而不必使用从加载表转换为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.

推荐答案

下面是一个示例，可以使您对可以做的事情有所了解。理想情况下，在SSIS程序包或任何ETL作业中，您应考虑到数据可能与您想要的数据不完全相同。您需要采取适当的步骤来处理可能不时弹出的不正确或无效数据。这就是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 Column transformation or Data conversion transformation to achieve your requirements.

该示例是在 SSIS 2008 R2 中创建的。它显示了如何读取包含日期的平面文件并加载到SQL表中。

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.

我创建了一个简单的SQL表来导入平面文件数据。

I created a simple SQL table to import the flat file data.

在SSIS软件包中，我有一个用于SQL的连接管理器，一个用于平面文件的连接管理器。平面文件连接的配置如下所示。

On the SSIS package, I have a connection manager to SQL and one for Flat file. Flat file connection is configured as shown below.

在在SSIS包中，我在控制流选项卡上放置了数据流任务。在数据流任务内部，我有一个平面文件源，派生列转换和一个OLE DB目标。由于平面文件源和OLE DB目标很简单，因此在此将其省略。派生转换将创建一个新列，其表达式为（DT_DBDATE）SmallDate 。请注意，您还可以使用数据转换转换执行相同的操作。此新列SmallDateTimeValue应该映射到OLE DB目标中的数据库列。

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.

在您的情况下失败的原因是因为无效数据直接插入到表中。在您的情况下，该表将引发异常，使程序包失败。在此示例中，包失败，因为如果有任何错误，则派生列转换的默认设置将使组件失败。因此，让我们进行一个虚拟转换以重定向错误行。为此，我们将进行多播转换。它实际上不会做任何事情。理想情况下，应该使用OLE DB目标或您选择的其他目标组件将错误行重定向到另一个表，以便您可以分析导致错误的数据。

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.

拖动派生转换中的红色箭头，并将其连接到多播转换。这将弹出配置错误输出对话框。将错误和截断列下的值从失败组件更改为重定向行。这样会将所有错误行重定向到Multicast转换，并且不会进入表中。

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.

这里是进入表的数据。只有2行有效。您可以查看显示文件中数据的第一个屏幕截图，您只会看到两行有效。

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.

希望可以给您一个实现以下要求的想法SSIS包。

Hope that gives you an idea to implement your requirement in the SSIS package.

这篇关于如何在数据导入期间将平面文件中的dd-mmm-yy值格式化为smalldatetime？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！