如何在SSIS中有条件分裂中使用类似条件

发布于 01-19 08:54 字数 446 浏览 3 评论 0原文

我正在使用条件拆分来从表中滤除数据。我有几个条件要检查。例如，在电子邮件列中，值不应包含〜字符并包含 @ sign。出生日期，年份不应大于当年，每月不应该大于31岁

SELECT *
FROM [table]
WHERE Email LIKE '%~%'
   OR NOT (Email LIKE "%@%")
   OR SUBSTRING(CONVERT(varchar, DOb, 23), 1, 2) <= 12
   OR SUBSTRING(CONVERT(varchar, DOb, 23), 4, 2) <= 31
   OR SUBSTRING(CONVERT(varchar, DOb, 23), 7, 4) <= YEAR(GETDATE());

。但是不确定如何使用条件分配在SSIS中实现它。

原文

I am using conditional split to filter out the data from table. I have couple of conditions to check for. For example, In Email column, value should not contain ~ character and contain @ sign. Date of birth column, year should not be greater than current year, month should not be greater that 12 and day should not be greater than 31.

SELECT *
FROM [table]
WHERE Email LIKE '%~%'
   OR NOT (Email LIKE "%@%")
   OR SUBSTRING(CONVERT(varchar, DOb, 23), 1, 2) <= 12
   OR SUBSTRING(CONVERT(varchar, DOb, 23), 4, 2) <= 31
   OR SUBSTRING(CONVERT(varchar, DOb, 23), 7, 4) <= YEAR(GETDATE());

I have above sql query for the same. But not sure how to implement it in SSIS using conditional split.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

埖埖迣鎅2025-01-26 08:54:51

根据您的条件在源查询中添加其他列，然后在条件 split 中使用这些列。

    SELECT CASE 
            WHEN Email LIKE '%@%'
                OR Email LIKE '%~%'
                THEN 'bad data'
            ELSE 'good data'
            END AS emaildatatype
        ,*
    FROM [table]
WHERE Email LIKE '%~%'
        OR NOT (Email LIKE "%@%")
        OR SUBSTRING(CONVERT(VARCHAR, DOb, 23), 1, 2) <= 12
        OR SUBSTRING(CONVERT(VARCHAR, DOb, 23), 4, 2) <= 31
        OR SUBSTRING(CONVERT(VARCHAR, DOb, 23), 7, 4) <= YEAR(GETDATE());

Add additional columns in your source query depending on your conditions and then use those columns in conditional split .

    SELECT CASE 
            WHEN Email LIKE '%@%'
                OR Email LIKE '%~%'
                THEN 'bad data'
            ELSE 'good data'
            END AS emaildatatype
        ,*
    FROM [table]
WHERE Email LIKE '%~%'
        OR NOT (Email LIKE "%@%")
        OR SUBSTRING(CONVERT(VARCHAR, DOb, 23), 1, 2) <= 12
        OR SUBSTRING(CONVERT(VARCHAR, DOb, 23), 4, 2) <= 31
        OR SUBSTRING(CONVERT(VARCHAR, DOb, 23), 7, 4) <= YEAR(GETDATE());

回复收藏 0 原文

~没有更多了~