DTS Conversion to SSIS Derived Column Expression

Question

I've been asked to convert a DTS package to SSIS. Both of which I am relatively new to, so I'm trying to learn both and how to convert between both at the same time.

We have a couple of transformations that I'm trying to change into Derived Column Expressions, but am struggling a bit with syntax.

We pull data from an excel sheet to SQL Server.

The script we used in the DTS package is as follows:

Function Main()
DTSDestination("Period") = Cint(Replace(DTSSource("Period"),"Q",""))
If not IsNull(DTSSource("Annual % Change")) Then
    If cStr(DTSSource("Annual % Change")) <> "." then
        DTSDestination("% Change") = cDbl(DTSSource("Annual % Change"))
    End If
End If

Main = DTSTransformStat_OK
End Function 

The output table is as follows:

[Period] [tinyint] NOT NULL,
[% Change] [decimal](7, 2) NULL,

I believe for the Period column it should just be (DT_I1)REPLACE([Period],"Q","")

For the [Annual % Change] Column, I've tried to break it down, which I think goes to :

NOT ISNULL([Annual % Change]) 
(DT_STR,10,1252)[Annual % Change] <> "." 
(DT_R8)[Annual % Change]

Then in trying to build a Conditional Expression would give me:

ISNULL([Annual % Change]) ? NULL(DT_R8) : 
    (DT_STR,10,1252)[Annual % Change] <> "." ? (DT_R8)[Annual % Change]: NULL(DT_R8)

This is throwing out parsing errors, any ideas where I am going wrong?

Thanks in advance.

Answer 1

In the SSIS Expression language, not equal is !=. <> doesn't work. I made that change to your expression, and it worked for me.

ISNULL([Annual % Change]) ? NULL(DT_R8) : 
(DT_STR,10,1252)[Annual % Change] != "." ? (DT_R8)[Annual % Change]: NULL(DT_R8)