Find RouteID from RouteName

I have Excel Input and SQL Server Output

The Excel provides a RouteName, I need to look up the RouteName in another SQL Table to find the RouteID, then output the RouteID.

I thought maybe Lookup Data Flow Transformation would do the trick, but this only looks up whether the row exists. Can anyone advise what Transformation is needed to convert RouteName into RouteID by looking up in a SQL Table?

Thanks

Richard

The lookup transform will also return a value from the table. In your scenario you should select the ID and Name from the SQL table in the first tab of the lookup. On the second tab, connect the name from the input (your excel spreadsheet) to the name on the lookup (the SQL table). Then check the ID column on the lookup. The ID column will be added to the data flow. When the data flow is executed, if the lookup finds a match on name, the ID column will be populated with the appropriate value.|||Thanks. That did the trick!

Filters Date

Hi:
How do I filter the output based on a date. I tried with CDate, without
CDate, with quotes, without quotes, putting time in the date. Nothing seems
to work. keep getting this error
Expression: =CDate(Fields!ISSUEDDATE.Value)
Value: CDate('1/1/2004')
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
The processing of filter expression for the table 'table1' cannot be
performed. The comparison failed. Please check the data type returned by
filter expression. (rsProcessingError) Get Online Help
Please suggest the write combination.
ThanksWhen doing expressions you need to use VB classes/methods. In particular
what you are looking for is the convert class.
The following will work:
expression Operator Value
=Fields!datetime.Value >
=convert.ToDatetime("9/17/2004 00:00:00")
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"NI" <NI@.discussions.microsoft.com> wrote in message
news:29C16728-4FD7-47E3-9B83-D4D5A71AA990@.microsoft.com...
> Hi:
> How do I filter the output based on a date. I tried with CDate, without
> CDate, with quotes, without quotes, putting time in the date. Nothing
seems
> to work. keep getting this error
> Expression: =CDate(Fields!ISSUEDDATE.Value)
> Value: CDate('1/1/2004')
>
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> The processing of filter expression for the table 'table1' cannot be
> performed. The comparison failed. Please check the data type returned by
> filter expression. (rsProcessingError) Get Online Help
> Please suggest the write combination.
> Thanks
>
>
>|||Thanks Bruce. That worked :)
"Bruce L-C [MVP]" wrote:
> When doing expressions you need to use VB classes/methods. In particular
> what you are looking for is the convert class.
> The following will work:
> expression Operator Value
> =Fields!datetime.Value >
> =convert.ToDatetime("9/17/2004 00:00:00")
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "NI" <NI@.discussions.microsoft.com> wrote in message
> news:29C16728-4FD7-47E3-9B83-D4D5A71AA990@.microsoft.com...
> > Hi:
> >
> > How do I filter the output based on a date. I tried with CDate, without
> > CDate, with quotes, without quotes, putting time in the date. Nothing
> seems
> > to work. keep getting this error
> >
> > Expression: =CDate(Fields!ISSUEDDATE.Value)
> > Value: CDate('1/1/2004')
> >
> >
> > An error has occurred during report processing. (rsProcessingAborted) Get
> > Online Help
> > The processing of filter expression for the table 'table1' cannot be
> > performed. The comparison failed. Please check the data type returned by
> > filter expression. (rsProcessingError) Get Online Help
> >
> > Please suggest the write combination.
> >
> > Thanks
> >
> >
> >
> >
> >
> >
>
>|||Bruce, that worked for me too.
However, I am trying to pass in parameter @.Start_Date.
I have tried the following and I cannot get it to work.
convert.ToDatetime("@.Start_Date 00:00:00")
convert.ToDatetime(@.Start_Date + " 00:00:00")
convert.ToDatetime(Start_Date + " 00:00:00")
Any ideas?
Thanks.
"Bruce L-C [MVP]" wrote:
> When doing expressions you need to use VB classes/methods. In particular
> what you are looking for is the convert class.
> The following will work:
> expression Operator Value
> =Fields!datetime.Value >
> =convert.ToDatetime("9/17/2004 00:00:00")
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "NI" <NI@.discussions.microsoft.com> wrote in message
> news:29C16728-4FD7-47E3-9B83-D4D5A71AA990@.microsoft.com...
> > Hi:
> >
> > How do I filter the output based on a date. I tried with CDate, without
> > CDate, with quotes, without quotes, putting time in the date. Nothing
> seems
> > to work. keep getting this error
> >
> > Expression: =CDate(Fields!ISSUEDDATE.Value)
> > Value: CDate('1/1/2004')
> >
> >
> > An error has occurred during report processing. (rsProcessingAborted) Get
> > Online Help
> > The processing of filter expression for the table 'table1' cannot be
> > performed. The comparison failed. Please check the data type returned by
> > filter expression. (rsProcessingError) Get Online Help
> >
> > Please suggest the write combination.
> >
> > Thanks
> >
> >
> >
> >
> >
> >
>
>