在 SSIS 中映射时如何检查条件？

发布于 2024-12-07 22:48:12 字数 759 浏览 1 评论 0原文

我想创建一个 ssis 包，它从平面文件中获取值并将其插入数据库表中，具体取决于companyname。

例如：

我有表字段：

Date        SecurityId   SecurityType   EntryPrice Price  CompanyName
2011-08-31  5033048      Bond           1.05       NULL   ABC Corp

现在我想将 Price 插入到该表中，但我需要与 CompanyName 匹配并且在文件 CompanyName 中也类似于 ABC 那么我如何检查并仅插入特定数据...... 像这样，我的文件中有 20 条具有不同公司名称的记录。

我确实喜欢这个在此处输入图像描述

在查找中我做了在此处输入图像描述

现在我的问题是我需要从平面文件中检查公司名称并将该公司价格插入表中，但在平面文件中公司名称的给出类似于“AK STL” ans桌子就像“AK STEEL CORPORATION”，所以为此我使用了列转换，但是我写什么表达式来查找匹配...与其他公司名称相同，平面文件中只有 1 英尺 2-3 个字符，请帮助

原文

i want to create one ssis package which takes values from flat file and insert it into database table depending upon there companyname.

for example:

I have table fields:

Date        SecurityId   SecurityType   EntryPrice Price  CompanyName
2011-08-31  5033048      Bond           1.05       NULL   ABC Corp

now i want to insert Price into this table but i need to match with CompanyName
and in that also in file CompanyName is like ABC so how can i checked that and insert only particular data...
like this i have 20 records in my file with different company names.

I DID LIKE THIS enter image description here

in lookup i did enter image description here

and now my problem is i need to check company name from flat file and insert that company price into table but in flat file company name is given like 'AK STL' ans in table it is like 'AK STEEL CORPORATION' so for this i have used column transformation but what expression i write to find match ...same with other company names only 1ft 2-3 charachters are there in flat file please help

分享到QQ

分享到微博

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

发布评论

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

剧终人散尽 2024-12-14 22:48:12

基本上，您希望将数据“更新插入”到数据库中。这里是一个简单的外观向上插入示例。正如您所说，数据集中的记录很少，这种方法就足够了。对于较大的数据集，您可能需要研究使用临时表并使用类似于以下的 SQL 逻辑：

--Insert Portion
INSERT INTO FinalTable
( Colums )
SELECT T.TempColumns
FROM TempTable T
WHERE
(
    SELECT 'Bam'
    FROM FinalTable F
    WHERE F.Key(s) = T.Key(s)
) IS NULL

--Update Portion
UPDATE FinalTable
SET NonKeyColumn(s) = T.TempNonKeyColumn(s)
FROM TempTable T
WHERE FinalTable.Key(s) = T.Key(s)
    AND CHECKSUM(FinalTable.NonKeyColumn(s)) <> CHECKSUM(T.NonKeyColumn(s))

Basically, you are looking to "Upsert" your data into the database. Here is a simple look up upsert example. With as few of records in your dataset as you have said, this method will suffice. With larger datasets, you probably want to look into using temp tables and using sql logic similar to this:

--Insert Portion
INSERT INTO FinalTable
( Colums )
SELECT T.TempColumns
FROM TempTable T
WHERE
(
    SELECT 'Bam'
    FROM FinalTable F
    WHERE F.Key(s) = T.Key(s)
) IS NULL

--Update Portion
UPDATE FinalTable
SET NonKeyColumn(s) = T.TempNonKeyColumn(s)
FROM TempTable T
WHERE FinalTable.Key(s) = T.Key(s)
    AND CHECKSUM(FinalTable.NonKeyColumn(s)) <> CHECKSUM(T.NonKeyColumn(s))

回复收藏 0 原文

~没有更多了~