CASE staments in SSIS - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

CASE staments in SSIS

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-05-05 : 11:38:03

i have this CASE

CASE WHEN [Type]='c' THEN 1 ELSE 0 End

In SSIS i write like that: [Type]=="c" ? 1 : 0

what if i have a complicated case like that:

CASE WHEN [Type]='y' THEN 'yellow'
     WHEN [Type]='r' THEN 'red'
     WHEN [Type]='b' THEN 'Black'
     WHEN [Type]='p' THEN 'pink'
     WHEN [Type]='o' THEN 'Orange'
     WHEN [Type]='Bl' THEN 'Blue'
     WHEN [Type]='Bl' THEN 'Blue'
     WHEN [Type]='w' THEN 'White'
     WHEN [Type]='g' THEN 'green'
     WHEN [Type]='pu' THEN 'purple'
ELSE IsNull('Unknown')
End

if i write like SSIS,i get complicated unreadable sentence ,have a better way to writte that in SSIS?

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2009-05-05 : 16:21:19

Use Script Component. You can keep your logic and more.

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-05-06 : 07:04:04

how?

can you give me example please?

is the way in ssis?

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2009-05-06 : 08:12:57

Sample:


Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
    Inherits UserComponent

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

        Select Case Row.Gender

            Case Is = "M"
                With Output0Buffer
                    .AddRow()
                    .Genderout = "MALE"
                End With

            Case Is = "M"
                With Output0Buffer
                    .AddRow()
                    .Genderout = "FEMALE"
                End With

            Case Else
                With Output0Buffer
                    .AddRow()
                    .Genderout = "ALIEN"
                End With

                '    CASE WHEN [Type]='y' THEN 'yellow'
                '     WHEN [Type]='r' THEN 'red'
                '     WHEN [Type]='b' THEN 'Black'
                '     WHEN [Type]='p' THEN 'pink'
                '     WHEN [Type]='o' THEN 'Orange'
                '     WHEN [Type]='Bl' THEN 'Blue'
                '     WHEN [Type]='Bl' THEN 'Blue'
                '     WHEN [Type]='w' THEN 'White'
                '     WHEN [Type]='g' THEN 'green'
                '     WHEN [Type]='pu' THEN 'purple'
                'ELSE IsNull('Unknown')

        End Select

    End Sub

End Class

Here's a video on how to setup Script Component:
http://www.jumpstarttv.com/Player.aspx?vid=6&plid=&searchid=33341

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-05-07 : 02:47:50

thanks,but
what microsoft recommended?
i want to bulit with featues in ssis?

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2009-05-07 : 07:19:21

If you want "readable" then that's it, else you use Derived Column which you end up with very long string. It's up to you.

You might want to use T-SQL instead.

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-05-07 : 11:54:21

if i use T-SQL in ssis , so i need to choice "SQL Task" and wrtie UPDAE,am i right?

UPDATE TABLE1
SET ColumnA=CASE WHEN [Type]='y' THEN 'yellow'
                 WHEN [Type]='r' THEN 'red'
                 WHEN [Type]='b' THEN 'Black'
                 WHEN [Type]='p' THEN 'pink'
                 ............ 
             ELSE IsNull('Unknown')
             End

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2009-05-07 : 12:05:17

Yah definitely.

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-05-11 : 11:15:01

sorry,but i dont like the ways that we use for the CASE.
i explain:

1.i dont want to use Script,because we use ssis .
2.in [b]Execute SQL Tsk[/b, i need to use with Case very colplicate that use with some JOIN.(and have UPDATE-cost a lot)

Like this code:

UPDATE TABLE1
SET ColumanA=
CASE

WHEN MyInput="1" THEN "A"
WHEN MySecondInput="1" THEN "B"
WHEN MyInput="2" AND MyColor="Red" THEN "C"
ELSE "F" END
FROM TABLE_A Join B On ... JOIN TABLE c On ....

maybe have another option?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources