从 SSIS 中加载的数据集中过滤掉重复项
我正在 SSIS 中进行一些 ETL 来构建一些维度数据集。其中之一是约会。为维度生成一组日期时，我可以对日期维度中已有的内容进行查找，并重定向任何失败的日期，这些日期被假定为新日期，然后添加到表中。
问题是我得到的数据集本身可能包含重复的日期。当插入维度表时，这将导致唯一日期键出现错误。因此，我正在寻找一种方法来过滤 SSIS 管道中加载的数据集。
我可以在初始加载日期时使用 DISTINCT，但本例中的日期是 DATETIME。稍后我需要使用数据转换，只需获取日期组件即可将其转换为日期。我正在寻找独特的日子，而 DATETIME 上的独特日期不会给我这样的机会。
我无法像以前那样使用 SSIS 查找，因为这需要一个指向数据库的连接管理器。
我可以告诉 OLE DB 目标不要使用批量插入，忽略任何错误。然而，这假设唯一的错误是重复的日期。
我对 SSIS 还很陌生，还没有找到一个转换工具可以让我与集合中的其他行进行比较。
I'm doing some ETL in SSIS to build some dimensional data sets. One of these is a date. When generating a set of dates for the dimension I can use a lookup against what's already in the date dimension and redirect any that fail, which are assumed to be new dates and then get added to the table.
Problem is the dataset that I've got might itself contain duplicate dates. This will cause errors with unique date keys when inserting into the dimension table. So I'm looking for a way to filter within the dataset that is loaded in the SSIS pipeline.
I could use DISTINCT on the initial loading of date but the date in this case is a DATETIME. I need to use a data conversion transformation later to turn this into a DATE by just taking the date component. I'm looking for unique days and a distinct on a DATETIME won't give me that.
I can't use SSIS lookup as I have before as that requires a connection manager that points to a database.
I could tell the OLE DB destination to not use bulk insert ignore any errors. This assumes however that the only errors will be duplicate dates.
I'm pretty new to SSIS and haven't been able to find a transformation tool that will allow me to compare to other rows in the set.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您可以使用排序转换并选择删除重复项，也可以使用聚合转换并仅使用分组依据（这或多或少类似于 DISTINCT）。请注意，这些操作是异步的，这意味着所有行在继续之前都必须进入此任务，这与同步任务相反，同步任务只是在行进入时吃掉并吐出行的缓冲区。
You can either use a Sort Transformation and select remove duplicates, or you can use the Aggregate transformation and only use group by (which will be more or less like a DISTINCT). Note that these operations are async, meaning all rows must enter this task before they continue, as opposed to sync tasks that just eats and spits out buffers of rows as they come in.