How to cast empty string

I want to replace a column value with a null if the string is empty. I would have thought this simple expression would do it:

RTRIM([FromContractSymbol]) == "" ? NULL(DT_STR, 0, 1252) : (DT_STR, 6, 1252)FromContractSymbol

Yet, I get the following error:

For operands of the conditional operator, the data type DT_STR is supported only for input columns and cast operators. The expression "...see above..." has a DT_STR operand that is not an input column or the result of a cast, and cannot be used with the conditional operation.

The expression works if I replace the NULL(DT_STR, 0, 1252) with say "A" and the expression works on other non-string columns. (As in "NULL(DT_I1) : (DT_I1)100")

The error does explain how to solve it. Although you have specified the type for the NULL you have to cast it.

So if you change your line to

RTRIM([FromContractSymbol]) == "" ? (DT_STR, 6, 1252)NULL(DT_STR, 6, 1252) : (DT_STR, 6, 1252)FromContractSymbol

It should work

How to call a UDF from expression language?

How to call a UDF from expression language?
(This should help work around the lack of reusability in SSIS, and also the problem that the validator doesn't understand length limits, and I have an incorrect validation warning beause the parse doesn't understand REPLICATE correctly, AFAICT.)
You can't do this Perry.

I dare say extensibility of the expression language is on the cards for the next version.

-Jamie

How to call a stored proc?

What is the syntax to call a SQL server stored procedure from a report?
Say if I wanted to create a custom field that would be based on an
expression that calls the stored proc (which would return a single
value from a single field - a sum in this case).
What would be the syntax for doing this within a UDF?
Thanks for any help!Hi
it very similar with sending an sql statment.
from the new dataset window choose command type=storsd procedure, and press
OK.
now you will get the stored procedures list. select the wanted SP and run
the query.
now, go back to the dataset window, you will find there your SP parameters.
you can give some values to those parameters.
hope it was helpful
Shlomi
"megabyte" wrote:
> What is the syntax to call a SQL server stored procedure from a report?
> Say if I wanted to create a custom field that would be based on an
> expression that calls the stored proc (which would return a single
> value from a single field - a sum in this case).
> What would be the syntax for doing this within a UDF?
> Thanks for any help!
>|||Thanks for your reply - I think maybe I did not explain this well
enough: The place where I need to call the stored proc is not while
building the original datasets for the report - this stored proc needs
to run during the execution of the report, for each record returned by
one of the datasets - so really the best place for me to call it would
be in the custom "code" section of the report, in a
User-Defined-Function - do you know what the syntax would be for that?
(this stored proc would return one single value, a sum).
Shlomi wrote:
> Hi
> it very similar with sending an sql statment.
> from the new dataset window choose command type=storsd procedure, and press
> OK.
> now you will get the stored procedures list. select the wanted SP and run
> the query.
> now, go back to the dataset window, you will find there your SP parameters.
> you can give some values to those parameters.
>
> hope it was helpful
> Shlomi
>
> "megabyte" wrote:
> > What is the syntax to call a SQL server stored procedure from a report?
> > Say if I wanted to create a custom field that would be based on an
> > expression that calls the stored proc (which would return a single
> > value from a single field - a sum in this case).
> >
> > What would be the syntax for doing this within a UDF?
> >
> > Thanks for any help!
> >
> >|||Hi
I can think about two ways to do this:
1. you can write a custom code for reading from DB, and call it from the
development envionment.
2. you can retrive all data and write client function for dealing the
aggregations. (i did dit once by retrive an XML and write some XML function
to deal with the aggregation).
if you need the exact solution, i'll try to hel you.
Shlomi
"megabyte" wrote:
> Thanks for your reply - I think maybe I did not explain this well
> enough: The place where I need to call the stored proc is not while
> building the original datasets for the report - this stored proc needs
> to run during the execution of the report, for each record returned by
> one of the datasets - so really the best place for me to call it would
> be in the custom "code" section of the report, in a
> User-Defined-Function - do you know what the syntax would be for that?
> (this stored proc would return one single value, a sum).
>
> Shlomi wrote:
> > Hi
> > it very similar with sending an sql statment.
> > from the new dataset window choose command type=storsd procedure, and press
> > OK.
> > now you will get the stored procedures list. select the wanted SP and run
> > the query.
> > now, go back to the dataset window, you will find there your SP parameters.
> > you can give some values to those parameters.
> >
> >
> > hope it was helpful
> >
> > Shlomi
> >
> >
> > "megabyte" wrote:
> >
> > > What is the syntax to call a SQL server stored procedure from a report?
> > > Say if I wanted to create a custom field that would be based on an
> > > expression that calls the stored proc (which would return a single
> > > value from a single field - a sum in this case).
> > >
> > > What would be the syntax for doing this within a UDF?
> > >
> > > Thanks for any help!
> > >
> > >
>|||Yes, 1 is what I'm after - writing custom code for reading from the DB
at runtime and calling it from the dev. environment - that's exactly
what I don't know how to do - it's VB.Net language, right? I have no
idea how to do that - do you? Do you know of sample code out there?
Shlomi wrote:
> Hi
> I can think about two ways to do this:
> 1. you can write a custom code for reading from DB, and call it from the
> development envionment.
> 2. you can retrive all data and write client function for dealing the
> aggregations. (i did dit once by retrive an XML and write some XML function
> to deal with the aggregation).
> if you need the exact solution, i'll try to hel you.
> Shlomi
> "megabyte" wrote:
> > Thanks for your reply - I think maybe I did not explain this well
> > enough: The place where I need to call the stored proc is not while
> > building the original datasets for the report - this stored proc needs
> > to run during the execution of the report, for each record returned by
> > one of the datasets - so really the best place for me to call it would
> > be in the custom "code" section of the report, in a
> > User-Defined-Function - do you know what the syntax would be for that?
> > (this stored proc would return one single value, a sum).
> >
> >
> > Shlomi wrote:
> > > Hi
> > > it very similar with sending an sql statment.
> > > from the new dataset window choose command type=storsd procedure, and press
> > > OK.
> > > now you will get the stored procedures list. select the wanted SP and run
> > > the query.
> > > now, go back to the dataset window, you will find there your SP parameters.
> > > you can give some values to those parameters.
> > >
> > >
> > > hope it was helpful
> > >
> > > Shlomi
> > >
> > >
> > > "megabyte" wrote:
> > >
> > > > What is the syntax to call a SQL server stored procedure from a report?
> > > > Say if I wanted to create a custom field that would be based on an
> > > > expression that calls the stored proc (which would return a single
> > > > value from a single field - a sum in this case).
> > > >
> > > > What would be the syntax for doing this within a UDF?
> > > >
> > > > Thanks for any help!
> > > >
> > > >
> >
> >|||Hi
This MSDN article will tell you everything you want to know about it:
http://msdn2.microsoft.com/en-us/library/ms153561.aspx
if you have more questions, you will be more than welcome.
P.S.
Use the Microsoft enterprise library to read your data.
"megabyte" wrote:
> Yes, 1 is what I'm after - writing custom code for reading from the DB
> at runtime and calling it from the dev. environment - that's exactly
> what I don't know how to do - it's VB.Net language, right? I have no
> idea how to do that - do you? Do you know of sample code out there?
>
> Shlomi wrote:
> > Hi
> > I can think about two ways to do this:
> > 1. you can write a custom code for reading from DB, and call it from the
> > development envionment.
> >
> > 2. you can retrive all data and write client function for dealing the
> > aggregations. (i did dit once by retrive an XML and write some XML function
> > to deal with the aggregation).
> >
> > if you need the exact solution, i'll try to hel you.
> >
> > Shlomi
> >
> > "megabyte" wrote:
> >
> > > Thanks for your reply - I think maybe I did not explain this well
> > > enough: The place where I need to call the stored proc is not while
> > > building the original datasets for the report - this stored proc needs
> > > to run during the execution of the report, for each record returned by
> > > one of the datasets - so really the best place for me to call it would
> > > be in the custom "code" section of the report, in a
> > > User-Defined-Function - do you know what the syntax would be for that?
> > > (this stored proc would return one single value, a sum).
> > >
> > >
> > > Shlomi wrote:
> > > > Hi
> > > > it very similar with sending an sql statment.
> > > > from the new dataset window choose command type=storsd procedure, and press
> > > > OK.
> > > > now you will get the stored procedures list. select the wanted SP and run
> > > > the query.
> > > > now, go back to the dataset window, you will find there your SP parameters.
> > > > you can give some values to those parameters.
> > > >
> > > >
> > > > hope it was helpful
> > > >
> > > > Shlomi
> > > >
> > > >
> > > > "megabyte" wrote:
> > > >
> > > > > What is the syntax to call a SQL server stored procedure from a report?
> > > > > Say if I wanted to create a custom field that would be based on an
> > > > > expression that calls the stored proc (which would return a single
> > > > > value from a single field - a sum in this case).
> > > > >
> > > > > What would be the syntax for doing this within a UDF?
> > > > >
> > > > > Thanks for any help!
> > > > >
> > > > >
> > >
> > >
>

How to build this expression?

Greetings friends,

I have the following T-SQL CASE statement. I've spent the last 10 minutes trying to convert it to an expression in my derived column component but to no avail.

case
when f.etypeid < 10 then '000' + cast(f.etypeid as varchar)
when f.etypeid > 10 and f.etypeid < 100 then '00' + cast(f.etypeid as varchar)
else
'0' + cast(f.etypeid as varchar)
end

Many thanks for your help in advance.

Hi again guys,

Finally I managed to work it out. Silly me!

The solution to the above is as follows :

etypeid < 10 ? "000" + (dt_str,1,1252)etypeid : etypeid > 10 && etypeid < 100 ? "00" + (dt_str,2,1252)etypeid : "0" + (dt_str,3,1252)etypeid

Sorry for the bother SSIS friends

|||Mark your post as an answer, please.

How to build an expression for a textbox?

Can I build an expression on a textbox with a "Sql Select" statement based
on the dataset? There is a parameter user selected on the dropdown and it
generates a dataset from the store procedure. This parameter is used on a
subquery for this dataset in the store procedure and it will return with the
records containing other parameter IDs. However I need to assign a record on
a textbox and require to filter out the record with the same parameterID
user selected in the dropdown. Thanks.
Eg. Parameters!Person.Value=2 selected in the dropdown
Recordset: SearchName
PersonID LName FName
1 Doe John
2 Doe Peter
3 Doe Jane
I need to assign the the name Doe, Peter on a textbox.If I understand correctly, you just want to use the data from the result set
that is returned from the data set.
If you go to the text box where you want to add the expression, right-click
that box, and click on the 'Expression' option.
In the Expression builder you can click the expansion (+) button next to
Fields, then select the Last Name, and click the insert button. Then in the
expression box, type a string that would add a comma, then finish by adding
the first name field to the expression box.
The result in the Expression box would look something like:
=Fields!LName.Value + ", " + Fields!FName.Value
Give that a try and see what happens.
"Paul" wrote:
> Can I build an expression on a textbox with a "Sql Select" statement based
> on the dataset? There is a parameter user selected on the dropdown and it
> generates a dataset from the store procedure. This parameter is used on a
> subquery for this dataset in the store procedure and it will return with the
> records containing other parameter IDs. However I need to assign a record on
> a textbox and require to filter out the record with the same parameterID
> user selected in the dropdown. Thanks.
> Eg. Parameters!Person.Value=2 selected in the dropdown
> Recordset: SearchName
> PersonID LName FName
> 1 Doe John
> 2 Doe Peter
> 3 Doe Jane
> I need to assign the the name Doe, Peter on a textbox.
>
>