How to check the Value exists in another Table

Question

How to check the Value exists in another Table

Guitar_player

SSCrazy

Points: 2991
More actions
November 17, 2014 at 1:45 am

#292563

Hi ,
I am kind of in Confusion in some simple logic i believe , so i need the help of you all .
My requirement is that I need to compare a value that i am getting from one table and compare that value whether exists in another table or not and i need to check this is Control Flow level and not in Data Flow
.
Ex: I am getting the year from one table and comparing whether that year value is existing in another table or not
If Existing then End my process
If Not Existing then continue with my next step..
Please help me out

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

Guitar_player (11/17/2014)
Hi ,
I am kind of in Confusion in some simple logic i believe , so i need the help of you all .
My requirement is that I need to compare a value that i am getting from one table and compare that value whether exists in another table or not and i need to check this is Control Flow level and not in Data Flow
.
Ex: I am getting the year from one table and comparing whether that year value is existing in another table or not
If Existing then End my process
If Not Existing then continue with my next step..
Please help me out

Quick suggestion, use an ExecuteSQL task in the control flow, i.e. "SELECT COUNT(*) FROM X WHERE X.YEAR = @PARAMETER"

😎

Ells SSCrazy Eights Points: 9901 More actions · Answer 2

Ells

SSCrazy Eights

Points: 9901

November 17, 2014 at 3:36 am

#1758911

sorry double posted

Ells SSCrazy Eights Points: 9901 More actions · Answer 3

Not sure if this is what you want but.

I built two date tables

select distinct [YEAR] from data.vwDimDate D

-- 2009 to 2016

select distinct [YEAR] from data.vwDimDate2 D

-- 2010 to 2017

So if you look at D2 then 2017 does not exist in D1

1. I created an Execute SQL statement

2. Paste in code like this

select

DISTINCT -- [D2.Year],

CASE WHEN ISNULL(D1.[YEAR],0) = 0 THEN 0

ELSE 1 END AS PkgVariable

FROM(Select distinct [Year] from data.vwDimDate2 )D2 LEFT JOIN

(Select distinct [Year] from data.vwDimDate )D1

ON D2.[Year] = D1.[YEAR]

Where D2.[Year] = 2017

we are looking for the Year in D1 table when D2 is 2017. Now I just did this to trigger a 0 but you can adjust this for whatever condition you are looking for. The important bit is it returns 0 to PkgVariable when not found and 1 to PkgVariable when found.

3. Create a package variable (I used PkgVariable)

3. On the Execute SQL task general tab set result set to Single Row.

4. On the result set tab click add

5. On the result set tab add Result Name as PkgVariable and if you click on the variable name drop down you can add User::PackageVariable

So right now you know if the match has been made

From the Exsecute SQL task add a predence constraint to the following task.

6. Edit the constraint and

set Evaluation operation to 'Expression and constraint'

set value to 'success'

set Expression to '@PkgVariable ==1'

Now the package will only proceed if the date you are interested in is in the other table.

Hope that helps

E

😎

Guitar_player SSCrazy Points: 2991 More actions · Answer 4

Hi ,

Thanks for the suggestions. I have achieve it somehow you can say by creating a function which just gives me a bit as the output and somehow i was able to solve it .

I will try the suggestions provided by you guys too .

Thank you for the help guys 🙂