October 11, 2010 at 9:23 am
I am using SSIS lookup transform to match an ID corresponding to the name.
The condtition is if the "Name" comes in as NULL then the "ID" should be NULL
Else get the mapped "ID" from the Deg table.
The Deg table does not holds any NULLs or unknowns.
Any help appreciated.
October 11, 2010 at 9:36 am
Not sure how this would work into your transformation but just going on what you've provided, the case statement would be something like this:
SELECT
CASEWHENNAMEIS NULL
THENNULL
ELSEID
ENDASDEG_ID
FROM DEG_TABLE
October 11, 2010 at 9:56 am
I need to do something like this but not getting the correct syntax:
SELECT CSP.P_Degree_Name
(CASE WHEN CSP.P_Degree IS NULL THEN NULL
ELSE
( SELECT CSP.P_Degree_Name,DEGREE_ID FROM dbo.Staging_Providers CSP INNER JOIN
dbo.Degrees D
ON D.Degree_Name = CSP.P_Degree)
END As Degree_ID)
FROM dbo.Staging_Providers CSP INNER JOIN
dbo.Degrees D
ON D.Degree_Name = CSP.Provider_Degree
i.e if P_Degree_Name from the dbo.Staging_Providers table is null the degree id sholud be inserted as null else match with thiss sql statement
SELECT CSP.P_Degree_Name,DEGREE_ID FROM dbo.Staging_Providers CSP INNER JOIN
dbo.Degrees D
ON D.Degree_Name = CSP.P_Degree
October 12, 2010 at 10:56 am
PSB (10/11/2010)
I am using SSIS lookup transform to match an ID corresponding to the name.The condtition is if the "Name" comes in as NULL then the "ID" should be NULL
Else get the mapped "ID" from the Deg table.
The Deg table does not holds any NULLs or unknowns.
Any help appreciated.
Why are you posting SSIS questions in a T-SQL forum?
All you need to do is set up the Error Configuration on your SSIS Lookup task to ignore errors. This essentially does a "left outer join" to the lookup table.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 12, 2010 at 12:08 pm
SELECT CSP.P_Degree_Name, D2.DegreeID
FROM dbo.Staging_Providers CSP
INNER JOIN dbo.Degrees D ON D.Degree_Name = CSP.Provider_Degree
LEFT OUTER JOIN dbo.Degrees D2 ON D2.Degree_Name = CSP.P_Degree
You don't really even need the CASE then because if CSP.P_Degree is NULL, D2.DegreeID will always be NULL also.
Scott Pletcher, SQL Server MVP 2008-2010
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply