Data Flow not working correctly

I am using SSS to move data from Oracle to SQL Server and I have the Oracle source with a SQL Server Dest and Im doing a dataflow task that uses a Data Conversion before the insert from Oracle to SQL(pic included). Every field gets inserted except for CR_TYPE . I have attached a pic of my data flow. I also attached a pic of the Data Conversion. The field sizes are the same(VARCHAR2(15) on Oracle side and VARCHAR(15) on SQL Side....Everything updates except for the CR_TYPE field. I cant figure out why though? Can someone help me with this?

THANKS
JK
DataFlow-task.png
Data-Conversion.png
jknj72Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
>Data Flow not working correctly
Please define 'not working correctly', preferably with an error message.  
And also send us a screen shot of the mapping to demonstrate that column CR_TYPE is being populated.
jknj72Author Commented:
I guess 'not working correctly' is not the correct thing to say. I am mapping the fields and everything works except for the CR_Type file(which allows Nulls). It is null for every row even though the values are 'C' or 'R' for every field in the source. The next step is an Execute SQL task that Updates the field from 'C or R' to 'Cancellation or Reinstatement'.
There is no error message, it finishes but without Inserting the values into the CR_Type field. I have a pic of the mappings. The Copy of fields are the fields generated from the Data Conversion. Thanks for the help
Mappings.png
jknj72Author Commented:
Heres what happened....
My source was a query
SELECT
CR_KEY,  
CASE WHEN CR_TYPE = 'C' THEN 'CANCELLATION' ELSE 'REINSTATEMENT' END CR_TYPE,  
CR_YR,
CR_MO,
CR_DA,
COV_EFFDT,
ACTION_EFFDT
FROM TEMP_BASE_PCOV_CANCREIN

This worked fine but I forgot the next step was an update trying to do the same thing. I tried to get rid of the update with my CASE WHEN statement but forgot I didn't need to run the next step.

So this ran and set everything was set to Null
Update Canc_Rein
Set Canc_Rein_Type = CASE When Canc_Rein_Type = 'C' Then 'Cancelation'
When Canc_Rein_Type = 'R' Then 'Reinstatement'
END

My problem with the whole thing is if it didn't meet the criteria in the Update then why did it set everything to Null? Its got me a little puzzled. I will give the points to whoever can tell me whats with the Nulls?

Thanks
JK
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Helena Markováprogrammer-analystCommented:
I am not familiar with MS SQL Server but what happened if there would be ELSE ? (I don't know if it can be there)

Update Canc_Rein
 Set Canc_Rein_Type = CASE When Canc_Rein_Type = 'C' Then 'Cancelation'
 When Canc_Rein_Type = 'R' Then 'Reinstatement'
ELSE
null

 END

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jknj72Author Commented:
Henka, Well that is what Ive found out. If you don't cover every avenue then I guess it defaults to NULL??
jknj72Author Commented:
thx
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.