Need to use Select Case VBScript statement in DTS Package

[uncleroydee]

Can anyone tell me the syntax for a Select Case statement in a VB data transformation script within a Data Transformation Service package? I've tried many different iterations, VB and SQL derivatives, to no avail. My latest iteration is this:
DTSDestination("Tst_Prfrm_YN&quot = SELECT CASE(DTSSource("Testconducted?&quot
when N then 0
when Y then 1
ELSE 0
END )

As an aside, the following works for a "Left" function in the same script, and it doesn't appear to be standard VBScript syntax:
Function Main()
DTSDestination("C_NBR_Agcy&quot = LEFT(DTSSource("Contract Number&quot,6)

Thanks,

Roy

 

[DougP]

this is right out of the "Book on line" from SQL

Use a SELECT statement with a simple CASE function
Within a SELECT statement, a simple CASE function allows only an equality check; no other comparisons are made. This example uses the CASE function to alter the display of book categories to make them more understandable.

USE pubs

GO

SELECT Category =

CASE type

WHEN 'popular_comp' THEN 'Popular Computing'

WHEN 'mod_cook' THEN 'Modern Cooking'

WHEN 'business' THEN 'Business'

WHEN 'psychology' THEN 'Psychology'

WHEN 'trad_cook' THEN 'Traditional Cooking'

ELSE 'Not yet categorized'

END,

CAST(title AS varchar(25)) AS 'Shortened Title',

price AS Price

FROM titles

WHERE price IS NOT NULL

ORDER BY type, price

COMPUTE AVG(price) BY type

GO
DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[uncleroydee]

Thanks Doug.

I've seen the BOL page you referenced, but it's a VBScript Select Case function in the DTS package (Transformations) that's causing me pain, not the T-SQL "CASE" statement.

I'm trying to use the Select Case function to transform data from an Access database as it is imported into SQL Server.

Thanks again for checking on this. Please let me know if you come up with anything else.

Roy