All it takes is practice. Game is different from say, DoD and Unreal Tournament. Just because game is different and you don't want to invest time/effort to practice, does not make CS:S a bad game. I have been playing for about 2 years (1.6, then Source), ran my own 1.6 "fun yard" server with crazy mods (was full 24/7), and then spent about 3 weeks adjusting to source, now I play maybe 8 hours a week, and stay near top of the list ranked by kills; My kill to death ratio is nearly always positive, around 1.5 kills for 1 death.
You don't have to play "real" maps - ever since GUN GAME was released on Source, I don't play normal maps anymore. Gungame is simply so awesome - turn off the brain and practice reaction/aim skillz. Rounds in gungame are very short, because maps are small and everyone rushes; gungame deathmatch is actually fun - you get new guns and not just more kills. There are several types of gungame, reverse and normal, turbo and regular.
My friend is a huge fan of zombie mod, but I don't play it.
That said, try gg_ maps - no skill required, but if you have skill, it's even better (noobs cry HACKER!).
Can't wait to get my own CS:S gungame server, but I heistate because there are plenty of awesome servers as is.