Derived Column Transformation Editor Question

Help...

I'm having trouble coming up with a valid expression in my derived column transformation editor that tests the input column for NULL and responds something like this:

if[message] isNull then "NA" else [message]

where [message] is the input column.

Thanks!

Try:

ISNULL([message]) ? "NA" : [message]

|||SWEET!! Thanks!

Derived Column Transformation Editor Question

Help...

I'm having trouble coming up with a valid expression in my derived column transformation editor that tests the input column for NULL and responds something like this:

if[message] isNull then "NA" else [message]

where [message] is the input column.

Thanks!

Try:

ISNULL([message]) ? "NA" : [message]

Derived Column can't add 8-byte integers

I'm having trouble adding a 4-byte integer with an 8-byte integer. Here's what I'm doing:

Column Name: BIG_ID

Derived Column: < add as new column >

Expression: (DT_I8)[ID] + 840230000538058

Data Type: eight-byte signed integer {DT_I8]

The error I get:

The literal 840230000538058 is too large to fit into type DT_I4. The magnitude of the literal overflows the type.

Then I try the expression:

(DT_I8)[ID] + (DT_I8)840230000538058

and

[ID] + 840230000538058

and get the same error.

What am I doing wrong? Is it possible to add 2 8-byte integers in regular expression? Why does it still think the literal is DT_I4?

Thanks,

Michael

I'm able to make this work by putting the large integer into a variable that's been typed as Int64. However, I'm still curious as to why I shouldn't be able to do this in the derived column transformation without using a variable?

Thanks,

Michael

|||Looks like a bug to me. I get the same behavior. Maybe someone from MS could check, or one of the MVPs could flag this for follow-up.|||

When you specify a numeric literal in an expression, it is by default typed as a DT_I4. If you want it to be a DT_I8, you must append an 'L' to the literal. For DT_UI8, append 'UL'.

So for your example, you should try the following expression: (DT_I8)[ID] + 840230000538058L

See the Books Online topic "Literals (SSIS)" for more details.

Thanks

Mark

|||Doh! That would explain it |||

Thanks!

-Michael

Deploying SSRS

I am trying to get SSRS to work and am having trouble getting it to go.
When I go to IIS and browse, it won't let me see the page, it reads "This
page cannot be displayed". The internet has connectivity because I can get
on my email and other sites.
When I try to deploy the report, I am having issues as well with
connectivity. I get an error that reads "A connection could not be made to
the report server http://localhost/ReportServer"
I went to services on the server and verified that SSRS was running and so
was IIS.
Any thoughts?Check the access (security) rights on the ReportServer.
David
"Ryan Mcbee" <RyanMcbee@.discussions.microsoft.com> wrote in message
news:ED56B7A0-274E-4345-914C-3AAFA0B4CF59@.microsoft.com...
>I am trying to get SSRS to work and am having trouble getting it to go.
> When I go to IIS and browse, it won't let me see the page, it reads "This
> page cannot be displayed". The internet has connectivity because I can
> get
> on my email and other sites.
> When I try to deploy the report, I am having issues as well with
> connectivity. I get an error that reads "A connection could not be made
> to
> the report server http://localhost/ReportServer"
> I went to services on the server and verified that SSRS was running and so
> was IIS.
> Any thoughts?|||David,
Do you mean the ReportServer db database in SQL management studio? What
security are you referring to?
Thanks,
Ryan
"David C" wrote:
> Check the access (security) rights on the ReportServer.
> David
> "Ryan Mcbee" <RyanMcbee@.discussions.microsoft.com> wrote in message
> news:ED56B7A0-274E-4345-914C-3AAFA0B4CF59@.microsoft.com...
> >I am trying to get SSRS to work and am having trouble getting it to go.
> > When I go to IIS and browse, it won't let me see the page, it reads "This
> > page cannot be displayed". The internet has connectivity because I can
> > get
> > on my email and other sites.
> >
> > When I try to deploy the report, I am having issues as well with
> > connectivity. I get an error that reads "A connection could not be made
> > to
> > the report server http://localhost/ReportServer"
> >
> > I went to services on the server and verified that SSRS was running and so
> > was IIS.
> >
> > Any thoughts?
>
>|||Go into the Report Manager from your browser. Select the folder that holds
your reports, click the Properties tab and then the Security link. Users
should have at least Browser (I think) to run the reports.
Use the "New Role Assignment" button to add users or groups. I suggest you
use an Active Directory (or local) group as it will be easier to manage.
David
"Ryan Mcbee" <RyanMcbee@.discussions.microsoft.com> wrote in message
news:AB4F831D-1CBB-4786-80B4-5747988BA44F@.microsoft.com...
> David,
> Do you mean the ReportServer db database in SQL management studio? What
> security are you referring to?
> Thanks,
> Ryan
> "David C" wrote:
>> Check the access (security) rights on the ReportServer.
>> David
>> "Ryan Mcbee" <RyanMcbee@.discussions.microsoft.com> wrote in message
>> news:ED56B7A0-274E-4345-914C-3AAFA0B4CF59@.microsoft.com...
>> >I am trying to get SSRS to work and am having trouble getting it to go.
>> > When I go to IIS and browse, it won't let me see the page, it reads
>> > "This
>> > page cannot be displayed". The internet has connectivity because I can
>> > get
>> > on my email and other sites.
>> >
>> > When I try to deploy the report, I am having issues as well with
>> > connectivity. I get an error that reads "A connection could not be
>> > made
>> > to
>> > the report server http://localhost/ReportServer"
>> >
>> > I went to services on the server and verified that SSRS was running and
>> > so
>> > was IIS.
>> >
>> > Any thoughts?
>>|||David,
That is the problem, I can't even get the browser page to show up. When I
try to deploy a report, it isn't connecting to the report server. Not sure
what is going on. IIS is on and so is SSRS services.
Any other thoughts?
Ryan
"David C" wrote:
> Go into the Report Manager from your browser. Select the folder that holds
> your reports, click the Properties tab and then the Security link. Users
> should have at least Browser (I think) to run the reports.
> Use the "New Role Assignment" button to add users or groups. I suggest you
> use an Active Directory (or local) group as it will be easier to manage.
> David
> "Ryan Mcbee" <RyanMcbee@.discussions.microsoft.com> wrote in message
> news:AB4F831D-1CBB-4786-80B4-5747988BA44F@.microsoft.com...
> > David,
> > Do you mean the ReportServer db database in SQL management studio? What
> > security are you referring to?
> >
> > Thanks,
> > Ryan
> >
> > "David C" wrote:
> >
> >> Check the access (security) rights on the ReportServer.
> >>
> >> David
> >> "Ryan Mcbee" <RyanMcbee@.discussions.microsoft.com> wrote in message
> >> news:ED56B7A0-274E-4345-914C-3AAFA0B4CF59@.microsoft.com...
> >> >I am trying to get SSRS to work and am having trouble getting it to go.
> >> > When I go to IIS and browse, it won't let me see the page, it reads
> >> > "This
> >> > page cannot be displayed". The internet has connectivity because I can
> >> > get
> >> > on my email and other sites.
> >> >
> >> > When I try to deploy the report, I am having issues as well with
> >> > connectivity. I get an error that reads "A connection could not be
> >> > made
> >> > to
> >> > the report server http://localhost/ReportServer"
> >> >
> >> > I went to services on the server and verified that SSRS was running and
> >> > so
> >> > was IIS.
> >> >
> >> > Any thoughts?
> >>
> >>
> >>
>
>|||Hi,
It seems to be a problem to hit the web server.
Have you check IIS logs ? Do you see connection attempts ? What is the HTTP
status code ?
Does SSRS and IIS on the same server ?
Jean-Pierre
"Ryan Mcbee" <RyanMcbee@.discussions.microsoft.com> wrote in message
news:CCE4F651-B458-4435-9FCA-0EFAE4235594@.microsoft.com...
> David,
> That is the problem, I can't even get the browser page to show up. When I
> try to deploy a report, it isn't connecting to the report server. Not
> sure
> what is going on. IIS is on and so is SSRS services.
> Any other thoughts?
> Ryan
> "David C" wrote:
>> Go into the Report Manager from your browser. Select the folder that
>> holds
>> your reports, click the Properties tab and then the Security link. Users
>> should have at least Browser (I think) to run the reports.
>> Use the "New Role Assignment" button to add users or groups. I suggest
>> you
>> use an Active Directory (or local) group as it will be easier to manage.
>> David
>> "Ryan Mcbee" <RyanMcbee@.discussions.microsoft.com> wrote in message
>> news:AB4F831D-1CBB-4786-80B4-5747988BA44F@.microsoft.com...
>> > David,
>> > Do you mean the ReportServer db database in SQL management studio?
>> > What
>> > security are you referring to?
>> >
>> > Thanks,
>> > Ryan
>> >
>> > "David C" wrote:
>> >
>> >> Check the access (security) rights on the ReportServer.
>> >>
>> >> David
>> >> "Ryan Mcbee" <RyanMcbee@.discussions.microsoft.com> wrote in message
>> >> news:ED56B7A0-274E-4345-914C-3AAFA0B4CF59@.microsoft.com...
>> >> >I am trying to get SSRS to work and am having trouble getting it to
>> >> >go.
>> >> > When I go to IIS and browse, it won't let me see the page, it reads
>> >> > "This
>> >> > page cannot be displayed". The internet has connectivity because I
>> >> > can
>> >> > get
>> >> > on my email and other sites.
>> >> >
>> >> > When I try to deploy the report, I am having issues as well with
>> >> > connectivity. I get an error that reads "A connection could not be
>> >> > made
>> >> > to
>> >> > the report server http://localhost/ReportServer"
>> >> >
>> >> > I went to services on the server and verified that SSRS was running
>> >> > and
>> >> > so
>> >> > was IIS.
>> >> >
>> >> > Any thoughts?
>> >>
>> >>
>> >>
>>

Deploying a Report

I'm having a little bit of trouble deploying the report project I have set up. In fact, I'm not even sure that deploying the project is what I want to do. All I want is to have something so I can view the report I've created in HTML or PDF form, something that was advertised when I downloaded the reporting services. Any help with what I would need with this would be appreciated.

Deploying your reports is what you need to do, but first you need to ensure that you have an instance of Report Server to deploy to - do you have that? Once you do, you just run the report, then you can specify whether you want to export it to PDF, Excel, etc.

|||I don't think I do have that. Or, if I do, I have no idea what it is. How would I get an instance of Report Server that I can deploy to?|||

So, it would seem that you have started up a new BI project, created some reports, and now need to make them visible to everyone? In that case, you need to find out from your DBA or sys admin where reporting services is installed (usually it resides on the same server as SQLServer), if you don't have one then you can install it from your SQLServer cd. Once done, you upload your datasource to it, and deploy your reports, set any rights you want on those reports (i.e. set the security on them), then to access your reports you would use a url similar to this: http://<your report server>/Reports/<any subfolder you created>/<your report name.htm>

Of course this is a very quick summary, you will find much more detail in BoL (Books On Line) and in the MSDN help (msdn.microsoft.com)

Hope that helps.

|||Yes, that definetly helps, thank you very much!