Jump to URL...

Hey All,
I have a report that tells the user all of the applied published fixes
that are out there. The report has an ODBC connect to a .txt file on a
defferent server. That is finally working great (i hate permissions
:)). Now I have to take the Fix_nbr field (which is a calculated field)
and add a hyperlink to it. User is to click on the fix number in the
report and it should go to the defferent server and pick up the HTM
file about that particular fix number and display it to the user. What
happens is when I click on the fix number, the sign in box for the
defferent server comes up, I sign in...then I get "Page Cannot be
Found...HTTP Error 404 - File or directory not found."
The expression below is what I am using and I have tried different
variations of this.
="http://cmbsssharrisdev/Fixes/Applied - Published/" &
Fields!Fix_Nbr.Value & ".htm"
Anyone have any suggestions of why this might be happening, any help
would be great!
I have to have this done by EOD tomorrow.
Thanks in advance!!!!
KerrieCheck in the IIS logs of the different server and see what URL you are
actually attempting to view. Could it be that you need to put %20 in for the
spaces in the url?
"Kerrie" wrote:
> Hey All,
> I have a report that tells the user all of the applied published fixes
> that are out there. The report has an ODBC connect to a .txt file on a
> defferent server. That is finally working great (i hate permissions
> :)). Now I have to take the Fix_nbr field (which is a calculated field)
> and add a hyperlink to it. User is to click on the fix number in the
> report and it should go to the defferent server and pick up the HTM
> file about that particular fix number and display it to the user. What
> happens is when I click on the fix number, the sign in box for the
> defferent server comes up, I sign in...then I get "Page Cannot be
> Found...HTTP Error 404 - File or directory not found."
> The expression below is what I am using and I have tried different
> variations of this.
> ="http://cmbsssharrisdev/Fixes/Applied - Published/" &
> Fields!Fix_Nbr.Value & ".htm"
> Anyone have any suggestions of why this might be happening, any help
> would be great!
> I have to have this done by EOD tomorrow.
> Thanks in advance!!!!
> Kerrie
>|||I tried what you have suggested and it did not help. Any other
suggestions?
Do you think maybe that it is a HTM file i am trying to get from a
shared folder from a different server?
lsmithmier wrote:
> Check in the IIS logs of the different server and see what URL you are
> actually attempting to view. Could it be that you need to put %20 in for the
> spaces in the url?
> "Kerrie" wrote:
> > Hey All,
> >
> > I have a report that tells the user all of the applied published fixes
> > that are out there. The report has an ODBC connect to a .txt file on a
> > defferent server. That is finally working great (i hate permissions
> > :)). Now I have to take the Fix_nbr field (which is a calculated field)
> > and add a hyperlink to it. User is to click on the fix number in the
> > report and it should go to the defferent server and pick up the HTM
> > file about that particular fix number and display it to the user. What
> > happens is when I click on the fix number, the sign in box for the
> > defferent server comes up, I sign in...then I get "Page Cannot be
> > Found...HTTP Error 404 - File or directory not found."
> >
> > The expression below is what I am using and I have tried different
> > variations of this.
> >
> > ="http://cmbsssharrisdev/Fixes/Applied - Published/" &
> > Fields!Fix_Nbr.Value & ".htm"
> >
> > Anyone have any suggestions of why this might be happening, any help
> > would be great!
> > I have to have this done by EOD tomorrow.
> >
> > Thanks in advance!!!!
> > Kerrie
> >
> >|||NEVERMIND...I GOT IT WORKING! The directory was not regisiter through
IIS!!!
THANKS SO MUCH FOR THE SUGGESTION!
Kerrie wrote:
> I tried what you have suggested and it did not help. Any other
> suggestions?
> Do you think maybe that it is a HTM file i am trying to get from a
> shared folder from a different server?
>
> lsmithmier wrote:
> > Check in the IIS logs of the different server and see what URL you are
> > actually attempting to view. Could it be that you need to put %20 in for the
> > spaces in the url?
> >
> > "Kerrie" wrote:
> >
> > > Hey All,
> > >
> > > I have a report that tells the user all of the applied published fixes
> > > that are out there. The report has an ODBC connect to a .txt file on a
> > > defferent server. That is finally working great (i hate permissions
> > > :)). Now I have to take the Fix_nbr field (which is a calculated field)
> > > and add a hyperlink to it. User is to click on the fix number in the
> > > report and it should go to the defferent server and pick up the HTM
> > > file about that particular fix number and display it to the user. What
> > > happens is when I click on the fix number, the sign in box for the
> > > defferent server comes up, I sign in...then I get "Page Cannot be
> > > Found...HTTP Error 404 - File or directory not found."
> > >
> > > The expression below is what I am using and I have tried different
> > > variations of this.
> > >
> > > ="http://cmbsssharrisdev/Fixes/Applied - Published/" &
> > > Fields!Fix_Nbr.Value & ".htm"
> > >
> > > Anyone have any suggestions of why this might be happening, any help
> > > would be great!
> > > I have to have this done by EOD tomorrow.
> > >
> > > Thanks in advance!!!!
> > > Kerrie
> > >
> > >

Jump to URL for pdf file

Does anyone know if it is possible to use the Jump To URL feature to display
a pdf file?
It seem like it should be possible because I can upload the pdf to a
Reporting Services folder and then use Report Manager to open and display it,
but I can't seem to get it to work in a report. Possibly due to a syntax
problem?...
Thanks,
SteveYes you can do it. I just tried on some word documents that I have deployed.
First, to make sure we are on the same page. You need to bring your PDF file
into your project and deploy it. Next do this:
="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/FolderName/Documentname.pdf','_blank'))"
or if don't want it open in another window do this:
=Globals!ReportServerUrl & "?/SomeFolder/SomeDocument.PDF"
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Steve" <MyNoSpam@.NoSpam.org> wrote in message
news:325FCAA0-0C98-48DE-A50F-28FCC264B48B@.microsoft.com...
> Does anyone know if it is possible to use the Jump To URL feature to
> display
> a pdf file?
> It seem like it should be possible because I can upload the pdf to a
> Reporting Services folder and then use Report Manager to open and display
> it,
> but I can't seem to get it to work in a report. Possibly due to a syntax
> problem?...
> Thanks,
> Steve|||Works great -- very cool. And I will use your open new window trick in a
different application. I was missing the "?".
Regards,
Steve
"Bruce L-C [MVP]" wrote:
> Yes you can do it. I just tried on some word documents that I have deployed.
> First, to make sure we are on the same page. You need to bring your PDF file
> into your project and deploy it. Next do this:
> ="javascript:void(window.open('" & Globals!ReportServerUrl &
> "?/FolderName/Documentname.pdf','_blank'))"
> or if don't want it open in another window do this:
> =Globals!ReportServerUrl & "?/SomeFolder/SomeDocument.PDF"
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Steve" <MyNoSpam@.NoSpam.org> wrote in message
> news:325FCAA0-0C98-48DE-A50F-28FCC264B48B@.microsoft.com...
> > Does anyone know if it is possible to use the Jump To URL feature to
> > display
> > a pdf file?
> >
> > It seem like it should be possible because I can upload the pdf to a
> > Reporting Services folder and then use Report Manager to open and display
> > it,
> > but I can't seem to get it to work in a report. Possibly due to a syntax
> > problem?...
> >
> > Thanks,
> > Steve
>
>

Jump to URL - check if valid file

Hello
I have a field in my dataset which could hold the path of a file
sometimes or would have some plain text. Before I give set that field
in the "Jump to URL", I want to check to see if it's a file and if so,
then assign that field for "Jump to URL" or leave it blank.
Is there any function to check if the value in the field is a file?
TIA
Sue..File you mean some text with ".extension" is that right ? then you can check
whether that extension exists using "INSTR" or if you are searching for the
path then you can search for "/" sign and then use "iif"
Amarnath, MCTS
"Sue" wrote:
> Hello
> I have a field in my dataset which could hold the path of a file
> sometimes or would have some plain text. Before I give set that field
> in the "Jump to URL", I want to check to see if it's a file and if so,
> then assign that field for "Jump to URL" or leave it blank.
> Is there any function to check if the value in the field is a file?
> TIA
> Sue..
>

Join two text files in SSIS

Hi,

I'm trying to join outputs of two text files combined in to one text file. The lenght of both the input text files are same (590)

For example,

If contents of text_file_1.txt is: aaaaaaaaaaaaaaaaaaaaaaaaaaaa

and contents of text_file_2.txt is: bbbbbbbbbbbbbbbbbbbbbbbbbbbb

My final text file should be :

final_text.txt

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

I use flat file source for both the input file and then use "Union all" and then write out to final text file destination.

The issues is:

The second file (bbbbbbbbbbbbbbbbbbbbbb) is coming up first in the output and then there is no carriage return.

My output looks like:

bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb...(goes up to 590)aaaaaaaaaaaaaaaaaaaa

aaaaaaaaaaaaaaaaaaa

What is that I'm missing here. Could some one plese help.

Thanks

Sri

This is a really odd request.

I would suggest that you not bother with the Union All step. You can't necessarily predict the order that the results of a union query will come out in. Better to process the first file and "Create" your output file from that, and then process the second file and "Append" the results to the first file.

The bigger question is why would you be doing this with SSIS at all?

Some clarification questions:

1. Does each file only contain one "line" of data (one record?)

2. Does either file have a carriage return at the end of the line already?

3. Is the length of the data fixed (590) or variable?

If you are still stuck, the clarification questions will help the rest of us come up with a better solution.

Dylan.

|||

It sounds like you are using a fixed width destination file. If you want a carriage return at the end of each row, use Ragged Right.

|||Good point, I forgot about that, but it won't fix the order of the result set.|||

Thanks Jwelch. Yes, I was using 'fixed with'. Ragged Right fixed the carriage return problem. Now my output file is properly aligned. But still I'm not able to control the sequence as Dylan72 pointed out.

The union all puts the contents of second file first and first file second.

Answers to Dylan72 question

1. Does each file only contain one "line" of data (one record?)

No. the fist file contains few hundred rows; the second file contains strictly one row (footer record)

2. Does either file have a carriage return at the end of the line already?

I used fixed width, {CR}(LF} for the flatfile destination.

3. Is the length of the data fixed (590) or variable?

The fist file is fixed 590, the second is 42 but I defined the output column width as 590

for the second file, so that both the files merge fine.

I know this is an odd request but that's how the vendor needs it.

Let me quickly summarize what I'm doing. To cut long story short,

In step 1, after a typical ETL process, the records (590 length) is written out to

a destination flat file (fixed width (now changed to ragged right), {CR}(LF}, column out put defined as 590). This step works well. I got the out put that I needed.

In Step2, vendor wants to add a footer record which is in the format

<20 Z's><record count, 11 positions><9's 11position> So a typical footer record

will look like:

ZZZZZZZZZZZZZZZZZZZZ0000000876599999999999

This step also works great, I got the desired output. The destination flat file for step2 is defined as the same like step1 (fixed with (now changed to ragged right), {CR}(LF}, column out put defined as 590)

So I got both the files in a way I want it.

Now I have to join both the text files so that contents of the first file appears first and the

footer record file (second file) is appended to the first file.

I used Union all to do this join, but not able to control the sequencing, always the footer record (second file) shows up first.

I hope I explained it clearly what I'm doing.

I will keep trying different things.

If anyone has a better approach let me know.

Thanks again Dylan72 and Jwelch for the valuable inputs.

-Sri

|||

Two ways I can think of to do this. One Dylan72 has already suggested - use two dataflows, one to process the first set of rows, and the second set to append the final row.

Two, if you really want to do this in one flow, use a Derived Column transform to add a column to both input files in the data flow. Set the column to a value of 1 for the first file, and a value of 2 for the second one. Run both through the Union All, then run the output of that through a Sort transform, using the new column. Then they will be in the proper order to write to the flat file. Just ignore the sort order column when mapping the columns in the flat file destination.

|||

Another approach is to use a script component to join the text files. This gives you the most control over the results. It requires some Visual Basic.net coding.

Here is an article demonstrating how to create a script source component (scroll down to the flat file example): http://technet.microsoft.com/en-us/library/ms136060.aspx

Join not returning records if one missing.

How do I set up my query to get data from a 2nd file when there may not be
any data?
For example, the following select just gets some data from the Position
table. The Category Description is in the JobCategory Table. I have the
CategoryID in the Position table.
Select PositionID,JobTitle,Category
from Position p
Join JobCategory j on p.categoryCode = j.categoryCode
where PositionID = 54
This works fine if the categoryCode happens to be in both tables. It may
not be there as it may be 0 (or null) if a code had not been chosen.
What I want to have happen is just have Category be blank if there is no
matching record.
What happens here is that I don't get the Position record either.
Thanks,
TomTry:
Select PositionID,JobTitle,Category
from Position p
Left Join JobCategory j on p.categoryCode = j.categoryCode
where p.PositionID = 54
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:u4uWz2gRFHA.3296@.TK2MSFTNGP15.phx.gbl...
How do I set up my query to get data from a 2nd file when there may not be
any data?
For example, the following select just gets some data from the Position
table. The Category Description is in the JobCategory Table. I have the
CategoryID in the Position table.
Select PositionID,JobTitle,Category
from Position p
Join JobCategory j on p.categoryCode = j.categoryCode
where PositionID = 54
This works fine if the categoryCode happens to be in both tables. It may
not be there as it may be 0 (or null) if a code had not been chosen.
What I want to have happen is just have Category be blank if there is no
matching record.
What happens here is that I don't get the Position record either.
Thanks,
Tom|||Witout DDL for the tables, it's hard to be sure, but I think what you are
trying to do would require an Outer Join. In an Outer Join, all the records
from one side will be produced, even if there's no match from the other side
on the Join condidtions
Select PositionID,JobTitle,Category
From Position p
Left Outer Join JobCategory j
On j.categoryCode = p.categoryCode
Where PositionID = 54
"tshad" wrote:

> How do I set up my query to get data from a 2nd file when there may not be
> any data?
> For example, the following select just gets some data from the Position
> table. The Category Description is in the JobCategory Table. I have the
> CategoryID in the Position table.
> Select PositionID,JobTitle,Category
> from Position p
> Join JobCategory j on p.categoryCode = j.categoryCode
> where PositionID = 54
> This works fine if the categoryCode happens to be in both tables. It may
> not be there as it may be 0 (or null) if a code had not been chosen.
> What I want to have happen is just have Category be blank if there is no
> matching record.
> What happens here is that I don't get the Position record either.
> Thanks,
> Tom
>
>|||Dear tshad,
Try following .....
-- If U want all records from Position
Select P.PositionID,J.JobTitle,J.Category from Position P
Left Join JobCategory J
on P.categoryCode = J.categoryCode
where P.PositionID = <<UrInput Value>>
-- If U want all records from JobCategory
Select P.PositionID,J.JobTitle,J.Category from Position P
Right Join JobCategory J
on P.categoryCode = J.categoryCode
where P.PositionID = <<UrInput Value>>
With Regards,
Rakesh Ranjan
Mail me on -- rakesh.ranjan@.3i-infotech.com
"tshad" wrote:

> How do I set up my query to get data from a 2nd file when there may not be
> any data?
> For example, the following select just gets some data from the Position
> table. The Category Description is in the JobCategory Table. I have the
> CategoryID in the Position table.
> Select PositionID,JobTitle,Category
> from Position p
> Join JobCategory j on p.categoryCode = j.categoryCode
> where PositionID = 54
> This works fine if the categoryCode happens to be in both tables. It may
> not be there as it may be 0 (or null) if a code had not been chosen.
> What I want to have happen is just have Category be blank if there is no
> matching record.
> What happens here is that I don't get the Position record either.
> Thanks,
> Tom
>
>