SSIS Expression Builder DT_WSTR to DT_NUMERIC Conversion

Question

SSIS Expression Builder DT_WSTR to DT_NUMERIC Conversion

mbokker

Say Hey Kid

Points: 698
More actions
November 4, 2009 at 12:06 pm

#156375

I'm at a loss to understand why I cannot convert a string variable to anything within Expression Builder.
(DT_NUMERIC, 10,0) [USER::sCount]
returns error code 0xc00470c2
Can ANYONE please help me with this? If this does not work, what are my options for converting a string to number so I can derive a new variable (var1-var2 = var3) within SSIS?

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 1

Where in SSIS are you trying to do this? In a data-flow? If so then use the data conversion transformation to do the convert. And then you can use a derived column to do the math. If possible do the math in the source SELECT.. Which reminds me, how are yo doing the source? Are you using an OLEDB source from a DB or is it from excel or such? If it is from a table are you specifying the SQL to do the select or did you just choose the table name from the list?

CEWII

mbokker Say Hey Kid Points: 698 More actions · Answer 2

I have two separate sources for the string counts stored within variables..cannot do within sql. It's coming from MSDAORA provider. I need to subtract one from the other and use the result as an input parameter to an Execute SQL task.

I really do not understand why I cannot cast this directly within the Expression Builder and set the results to a new variable. I would think this would be one of the main purposes for Expression Builder.

I'm very confused.

mbokker Say Hey Kid Points: 698 More actions · Answer 3

To answer your question on where I'm doing this...within Expression Builder on the Variable...EvaluateAsExpression for the creation of a new variable to store the difference between sVar1 and sVar2.

mbokker Say Hey Kid Points: 698 More actions · Answer 4

I'm going to give up on the Expression Builder.

Can you tell me how to use a variable as input to a Data Conversion task within a data flow?

Phil Parkin SSC Guru Points: 246695 More actions · Answer 5

Create a derived column & set it to the value of your variable before firing through data conv ...

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 6

You use a derived column task to get it into the pipeline and then you can select it in the data conversion task.

CEWII

Phil Parkin SSC Guru Points: 246695 More actions · Answer 7

Elliott W (11/4/2009)
You use a derived column task to get it into the pipeline and then you can select it in the data conversion task.
CEWII

Beat ya! 🙂

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

mbokker Say Hey Kid Points: 698 More actions · Answer 8

I'm new so not sure about how to do that.

When I add a Derived Column task to a Data Flow Task, it complains because there is no input columns. I want to use the two variables as input columns.

How do I do that and where do I do the math?

mbokker Say Hey Kid Points: 698 More actions · Answer 9

Can anyone else help me with this or point me to a link?

Need more info....

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 10

Phil Parkin (11/4/2009)
Elliott W (11/4/2009)
You use a derived column task to get it into the pipeline and then you can select it in the data conversion task.
CEWII
Beat ya! 🙂

I saw that..

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 11

mbokker (11/4/2009)
I'm new so not sure about how to do that.
When I add a Derived Column task to a Data Flow Task, it complains because there is no input columns. I want to use the two variables as input columns.
How do I do that and where do I do the math?

You don't have any other columns? You might create an OLEDB Source and put "SELECT Column1 = 1" into it, then you will have a pipeline and 1 row..

Are you only dealing with 1 row of data that is from variables?

If so I would probably build a Script Task to handle it. I could help you with that.

CEWII

mbokker Say Hey Kid Points: 698 More actions · Answer 12

Just thought I'd post here what the issue was in case some other newbie like myself encounters this.

When assigning a value from one variable to another and converting the type from one to the other, make sure the VALUE stored in the Variables table contains the type you want to convert to.

When I made this change, I was able to easily convert and do the math I needed in one step without having to create a Data Flow or a Derived Column or a conversion step.