SQL in OLE DB

Jim Work


Hey all!

Okay, this may not be the right forum, but I *think* it's an SSIS Question. If it belongs elsewhere, point the way and I'll repost it correctly.

I'm importing a flat file, and then I want to check to see if a column of the input matches a column of a reference table.

INSERT INTO BATCH
CASE
WHEN IN-REC-TYPE == BATCH.REC-TYPE
THEN (REC-TYPE, CYMD) VALUES (IN-REC-TYPE, IN-CYMD)
ELSE BLAHBLAHBLAH

Okay, so it doesn't like that bit in red up there. Is there some other fast way to do this In other words, how do i refer to my input columns within an OLE DB Command block

Thanks!

Jim Work

Phil Brammer


Use the Lookup Transformation.

Jim Work

Okay, I got the lookup table set up, but I'm not sure what to do with it. Let's say that I find that the record in question matches:

select * from
(select * from [dbo].[batch]) as refTable
where [refTable].[source_code] = {a parameterized input column - IN-SRC-CODE}

I don't want to select the rows where the source_code matches up. I just want to see if it's there. If it is, I want to then take that record and insert various parts of it into another table. If this returns NULL for a given record, then I want to create a new line in this table based on the triggering record.

What I'm really trying to do is figure out how to say, "Okay, does condition a exist, and if so execute this logic, but if not, execute this other logic."

Thanks for any further insights!

Jim Work

Phil Brammer

Just select the table that you want to use as your lookup. Select the checkbox for the key that you will be matching on. Don't mess with the SQL at all.

Then, map the input key column to the chosen key column from the lookup table. Stop there. That's all you have to do.

The green arrow coming out of the lookup transformation is for matches (typically used for updates -- because the data exists). The red arrow coming out of the lookup (usually an error output) indicates that a match was not found and is usually used for inserting data that does not exist.

Jim Work

Thanks a bunch for clearing that up!!