September 13, 2007 at 5:03 pm
Hi All,
I have a SSIS package that imports an Excel spreadsheet to a SQL Server database.
In the Excel Source Editor I selected SQL Command as the Data access model and the following statement does not work.
SELECT Name,
CASE Type_Cd WHEN "A" THEN "TEST" ELSE Type_Cd END Type
FROM [Sheet1$]
It works fine if I just have a select statement without the case function.
Thanks for your help.
September 14, 2007 at 4:29 am
Not sure what the limitations are for Jet but obviously it's not working.
Why not use a derived column and have the case statement in there?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
September 14, 2007 at 10:22 am
Crispin,
Yes I have a derived column that does it in the package. I was wondering if there is a work around if the case function does not work.
Thanks
Nate
September 14, 2007 at 2:36 pm
I think you're having trouble because you're using double-quotes instead of single quotes in your SQL statement.
case <field> When '<stringvalue>' then '<stringvalue>' END
If it IS Jet SQL - then you're looking at using IIF (that's the case replacement)
syntax IIF(<criterion>,value if true, value if false).
Native Excel calls that same thing an IF function (same syntax as above, except IF(....))
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply