datatype casting in derived column

Here is my expression in a derived component:

"Failed insert into PONL_WELL. WELL_NO=" + (DT_WSTR,10)PROP_NO

PROP_NO comes from ms sql server , and the derived component datatype for this column is DT_WSTR.

The destination will be ms sql server, and i have a data conversion after the derived component to cast from DT_WSTR to DT_STR.

However, the derived component failed everytime giving me

Error: 0xC0049064 at Load Ponl_Well, Derived Column [1342]: An error occurred while attempting to perform a type cast.

Anyone know how i can eliminate the data conversion component and just do my string and column concatenation in the derived column and have it output as DT_STR?

Sub-expressions and literals in a derived column expression are always DT_WSTR, but you can cast the expression result to DT_STR by wrapping the whole expression with a cast:

(DT_STR,<length>,<codepage>)("Failed insert into PONL_WELL. WELL_NO=" + (DT_WSTR,10)PROP_NO)

That should eliminate the need for the data convert transform after the derived column.

However, the error you listed looks like it was coming from the derived column, and I'm not sure why that would be the case if PROP_NO is of type DT_WSTR...

|||thanks for trying, but i'm still getting the same error msg, anyhow, i create a script component and concatenate the strings together, not the solution i wanted but it should work for now

DataTable query...

Hi...

In my appllication, i am having a DataTable... For that DataTable i have to write a query for a expression... This epression is more of mathematical expression type. Here is the sample expression-

(column1 < 1 && (column2 = 2 || column3 > 5) || column4 != 3)

How i can write a query for such a equation...? Is it possible to use logical & mathematical operator in the equation & to write a query for this type of equation... Is there is any good article on this...

Thanks in adavnce...

IamHuM

What exactly you need here. You can use the logical/math opertor as per your requirement.

Pls provide more clarity on your requirement.

|||

A good place to start is referring to Books Online, Topic 'Operators [Transact SQL]'.

Unfortunately, T-SQL does NOT have a full and rich set of mathematical operators. Depending upon your needs, you may be better served by exploring the capabilities of creating custom CLR functions (using VB.NET/C#.NET) and then having your SQL code use those CLR functions. A place to start with that is, again, Books Online, Topic: 'CLR User Defined Functions'.

As Mani indicated, if you post a more in-depth description of your requirements, we may be better able to guide you to the best resources.

|||

Code Snippet

select *

from
where Column1 < 1

and

(

(Column2 = 2 OR Column3 > 5)

OR

(Column4 <> 3)

)

Which is functionally equivalent to:

Code Snippet

select *

from <TableName>
where Column1 < 1

and

(

Column2 = 2

OR Column3 > 5

OR Column4 <> 3

)

Dataset Query Parameter

I have a dataset query that contains the expression:
Cost * (1 - ABS(SIGN(DATEPART(mm, OrderDate) - DATEPART(mm,
DATEADD(DateInterval.Month, - 11, { fn NOW() })))))
I have had no luck replacing the { fn NOW() } with a parameter and was
wondering if it was at all possible.To use parameters in a dataset query expression, use @.ParameterName.
"Todd Simmons" wrote:
> I have a dataset query that contains the expression:
> Cost * (1 - ABS(SIGN(DATEPART(mm, OrderDate) - DATEPART(mm,
> DATEADD(DateInterval.Month, - 11, { fn NOW() })))))
> I have had no luck replacing the { fn NOW() } with a parameter and was
> wondering if it was at all possible.
>|||I tried that and get a "Syntax error or access violation"
"Harolds" wrote:
> To use parameters in a dataset query expression, use @.ParameterName.
> "Todd Simmons" wrote:
> > I have a dataset query that contains the expression:
> >
> > Cost * (1 - ABS(SIGN(DATEPART(mm, OrderDate) - DATEPART(mm,
> > DATEADD(DateInterval.Month, - 11, { fn NOW() })))))
> >
> > I have had no luck replacing the { fn NOW() } with a parameter and was
> > wondering if it was at all possible.
> >

Datareader source/Data flow task property expression problems

Hi all,
I have the June CTP version of Yukon and it's various tools, and I'm having an issue with using property expressions or variable syntax within the SqlCommand string of a datareader source within a data flow task. It seems as if there were issues in past versions of doing this within data flow tasks, but I thought with the June CTP that this was a possibility. The documentation even states that you can do this within the sqlcommand property. Anybody else have this problem? Any solutions?
Thanks,
Adrian CrawfordHello Adrian,
Not sure exactly what you mean by "I'm having an issue with using property expressions or variable syntax" Are you referring to it error-ing out, or are the values not showing up properly?

Maybe the workaround in this post might help you?
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=70082

Jason|||Hey Jason,
Thanks for the reply. It is erroring out and not accepting my sqlcommand when I try to use any dts variables in the query. The datareader source works a little differently in that it doesn't give you the option to parse/build your query like other tasks do. Unfortunately there is not much of an error message to give. I get...
Further changes need to be made before the current settings can be saved to the component. Warnings reported by the component are:
And then nothing is listed. I'm trying to query an Oracle db and use dts vars in the query, but i have a feeling they are not getting parsed before being sent to Oracle.
Adrian
|||Hi Adrian,
Can you share your expression? I'd like to try to reproduce this so I can see why the error message is incorrect.
Also, what is the scope of the variables you are using?

Thanks
Mark|||Mark,
Thanks for your response. I figured out my problem in another recent post here:
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=73466
Basically I found that you can't directly enter variables or expressions in the sqlcommand box, since it will not parse it before being sent. I found the round about way in the above post.
Thanks,
Adrian