Generating and Printing Reports

I've created a data flow where I have linked to an OLE DB Source then created a Flat File Destination. My file is now on my c: drive. I'd like to use that data to create a report, then print to a PDF. How would I do that?

Currently I was doing this in Access, but I am moving my processes to SSIS.

You wouldn't use SSIS to do that. SSIS is an ETL tool, not a reporting tool.

-Jamie

|||But if I created my report in Reporting Services for instance, how could I put that in my data flow in SSIS?|||I was also assuming that I could put this in a Script Task, but I didn't know how far I could go with generating a report from there.|||

I don't know what you mean by "put a report in the data flow" or "put it in a script task". Can you expand on that?

There is a way that you can consume a SSIS dataflow from within SSRS but that doesn't seem to be what you're after here. You seem to want to build a report on top of a text file (that just happens to have been created by SSIS). I'm sorry, I don't understand what it is you actually want SSIS to do.

-Jamie

|||

I've gotten my answer:

Write an application in VB using the Report Viewer from the toolbar to create and print the report

Then execute that application from SSIS.

|||

So you're calling the SSRS API, is that correct? If so, you don't have to write another app to do that - you can embed it into SSIS.

-Jamie

|||What do you mean by calling the SSRS API? I'm using Visual Studio 2005.|||

ifaber wrote:

What do you mean by calling the SSRS API? I'm using Visual Studio 2005.

Shall we start again. What exactly is your VB app going to do?

|||My application is going to pull data from many tables, then export to a flat file. Our network person will pick up the files, and put the data on a website. Then from 2 of those tables, I pulled a list of instructors that currently is on our website in PDF reports. In Access, I have pulled the data, created a report and exported to a PDF. Our network person pulls those reports and puts them on the website. It's pretty simple compared to some other stuff I have done.|||

Fair enough, if it works for you that's good. I was just wondering if there was an option to utilise SQL Server Reporting Services (SSRS) seeing as that is a reporting application. I mistakenly assumed that seeing as you were using SSIS that you would be trying to use SSRS for reporting - my mistake. Sorry.

I also thought you were trying to automate the creation of PDF files - something that I suspect could be done from SSRS and ergo from SSIS if there is an API available for doing that. Again it looks like my assumption was wrong.

To me, it seems strange that you would use a differrent application to do all this when it can all be achieved with SSRS but then again I am a SQL Server zealot so I would say that :)

Good luck with it.

-Jamie

|||Wellllll, I know how to use SQL Server Reporting Services too. In fact, I have a lot of reports using SSRS. Pretty much, after I have updated the flat file, I'd like to run something that would create a report and would print to PDF.|||

Right. Well what I was driving at is if SSRS can create PDF reports (and I'm reliably informed that it can) then you can call the SSRS API from SSIS in order to create those reports for you. I haven't actually done it but I'm sure its possible.

-Jamie

|||Bingo!! Now how do I call the API :)|||

ifaber wrote:

Bingo!! Now how do I call the API :)

I was worried you were about to ask that.

I don't actually know, but I've no doubt that you can do it. To know more about the SSRS API you;d be best checking out the SSRS forum.

What I DO know is that SSRS itself is a web service which means that everything that can be done in SSRS can be called via that web service. essentially that web service IS the SSRS API.

You can calls web services from SSIS using the Web Service task. HOWEVER, I am aware that there are limitations with SSIS's web service task (search this forum to find out more) but don't despair because you have another optionavailable to you. You can call web services from within the SSIS script task.using dotnet APIs.

I hope that is useful and I hope you didn't mind me continuing the thread. Its just that you seemed to want to do all from within one package and I figured it would be possible. Anything that can be accessed via dotnet code can be done from SSIS's Script Task..

-Jamie

Generate linked series

Hi,
I would like to generate the following:
suppose we have the following table:
Table1:
Col1 Col2
a b
a c
d e
f c
d b
Then the result should give the following series:
b, a, c
a, b, d
a, c, f
e, d, b
b, a, c, f
Anyone an idea what would be the fastest way to resolve this?
Thanx in advance...
PeterThere are no built-in SQL statements which generates such series. The sample
data somewhat resembles hierarchical data representation. Please search
google for "Nested Sets" and "Path Enumeration" which are two commonly used
approaches to tackle such problems.
Anith|||You need to provide more info about what you want... I had a hard time even
discerning a pattern between the table data and the desired output, and the
one pattern I seemed to get doesn't make any sense...
(because of the fact that you have both "b, a, c" AND "b, a, c,, f" in
the output...)
"PeterM" wrote:

> Hi,
> I would like to generate the following:
> suppose we have the following table:
> Table1:
> Col1 Col2
> a b
> a c
> d e
> f c
> d b
> Then the result should give the following series:
> b, a, c
> a, b, d
> a, c, f
> e, d, b
> b, a, c, f
> Anyone an idea what would be the fastest way to resolve this?
> Thanx in advance...
> Peter
>|||Hi CBretana,
The logic behind it is the following:
I try to search for all possible length "paths" (until the longest path is
found);
these are created by using each row and see if one of the two columns in the
row also exist in another row, if this is the case I concatenate the result
as a "path" (and where the "link" value is only used once in the string).
I hope you see the point?
Peter
"CBretana" wrote:
> You need to provide more info about what you want... I had a hard time eve
n
> discerning a pattern between the table data and the desired output, and th
e
> one pattern I seemed to get doesn't make any sense...
> (because of the fact that you have both "b, a, c" AND "b, a, c,, f" in
> the output...)
> "PeterM" wrote:
>|||Hi,
Let me just explain where this would be useful:
by using the sp_fkeys on all tables in a relational design, I could build a
result table containing rows with all the PK_Table and their related FK_Tabl
e
info.
With this table I would search for all possible "paths" within my relational
design no matter the "depth" of the "path".
In this way I could easily generate, no matter which tables I would give as
input, the shortest path to connect these input tables together and make a
dynamic join statement.
That's why I'm really interested in a solution (preferably also performant).
Peter
"CBretana" wrote:
> You need to provide more info about what you want... I had a hard time eve
n
> discerning a pattern between the table data and the desired output, and th
e
> one pattern I seemed to get doesn't make any sense...
> (because of the fact that you have both "b, a, c" AND "b, a, c,, f" in
> the output...)
> "PeterM" wrote:
>|||Peter,
Don't you want to restrict the generation of these paths to go in one
direction only? what I mean is that a value in Column 1 of some row of the
table that also exists in Column 1 of another record is an intrinsically
different kind of relationship than when it exists in Column 2 of some other
row in the table.. Using the example you describe in yr oother post, one
column is PKs, and other would be FKs, right ? In that case would you ever
WANT to create "Path" that connects two values becase they were both in the
FK column of the table ' That would not imply any relationship at all...
"PeterM" wrote:
> Hi CBretana,
> The logic behind it is the following:
> I try to search for all possible length "paths" (until the longest path is
> found);
> these are created by using each row and see if one of the two columns in t
he
> row also exist in another row, if this is the case I concatenate the resul
t
> as a "path" (and where the "link" value is only used once in the string).
> I hope you see the point?
> Peter
> "CBretana" wrote:
>|||CBretana,
No I don't want any restriction because it is perfectly possible that two
tables are lnked to eachother by their FK_Table; how else would you represen
t
many to many relationships?
Peter
"CBretana" wrote:
> Peter,
> Don't you want to restrict the generation of these paths to go in one
> direction only? what I mean is that a value in Column 1 of some row of th
e
> table that also exists in Column 1 of another record is an intrinsically
> different kind of relationship than when it exists in Column 2 of some oth
er
> row in the table.. Using the example you describe in yr oother post, one
> column is PKs, and other would be FKs, right ? In that case would you ev
er
> WANT to create "Path" that connects two values becase they were both in t
he
> FK column of the table ' That would not imply any relationship at all...
> "PeterM" wrote:
>|||The right way to represent a many-to-many relationship is with a thord table
containing a multple-column (composite) primary Key, consisting of the Both
Primary Keys from the two other tables that have the many-to-many
relationship.
In this third table there is a single Composite PK, and 2 Foreign Keys, one
each to the PK of each of the two original tables.
"PeterM" wrote:
> CBretana,
> No I don't want any restriction because it is perfectly possible that two
> tables are lnked to eachother by their FK_Table; how else would you repres
ent
> many to many relationships?
> Peter
> "CBretana" wrote:
>|||CBrentana,
The third table you're talking about is exactly the FK_table of my example.
If you would run sp_fkeys on the PK_Tables the result would be two rows:
PK_Table1 - FK_Table
PK_Table2 - FK_Table
By linking the result of these two rows you would find the path to link
PK_Table1 with PK_Table2 (through the FK_Table in common).
which is exactly the point of depart.
So I hope you understand the question now (since you confirmed the point of
the many-to-many relationships).
Look I'm more interested in a solution of my question than in trying to
convince you in the "usefullness" of my question, if this would still be you
r
concern.;-)
Thanx...
Peter
"CBretana" wrote:
> The right way to represent a many-to-many relationship is with a thord tab
le
> containing a multple-column (composite) primary Key, consisting of the Bot
h
> Primary Keys from the two other tables that have the many-to-many
> relationship.
> In this third table there is a single Composite PK, and 2 Foreign Keys, on
e
> each to the PK of each of the two original tables.
>
> "PeterM" wrote:
>|||CBrentana,
In reviewing your answers I noticed we're both using different terms.
You're talking about FK_columns, where I'm talking about FK_Tables.
The columns of my examples contain Tables (PK_Tables or FK_Tables) and not
PK-FK columns (that would indeed make no sense). So instead of thinking in
terms of columns change to tables and you'll see that it makes sense :-)
Peter
"PeterM" wrote:
> CBrentana,
> The third table you're talking about is exactly the FK_table of my example
.
> If you would run sp_fkeys on the PK_Tables the result would be two rows:
> PK_Table1 - FK_Table
> PK_Table2 - FK_Table
> By linking the result of these two rows you would find the path to link
> PK_Table1 with PK_Table2 (through the FK_Table in common).
> which is exactly the point of depart.
> So I hope you understand the question now (since you confirmed the point o
f
> the many-to-many relationships).
> Look I'm more interested in a solution of my question than in trying to
> convince you in the "usefullness" of my question, if this would still be y
our
> concern.;-)
> Thanx...
> Peter
>
> "CBretana" wrote:
>