Report task?

Hello,
I'm not sure if this one should be in this forum. Anyway...
I need to build some reports in MS Word format. Is there are any way to
do that? It can probably can be done in Reporting Services, but how I can merge
these two tools in one project (solution) for example?
Thanks.You can have a SSRS project and a SSIS project in the same solution.

As an aside...you can also share data sources between those projects which is a handy feature.

-Jamie|||But how to do that? I couldn't any way (via set of menues) to add SSRS to SSIS solution.|||

Metal_Fly wrote:

But how to do that? I couldn't any way (via set of menues) to add SSRS to SSIS solution.

There is no such thing as an "SSIS solution", just an "SSIS Project". A solution is not limited to a single tool/technology.

I imagine that you can see your SSIS project in Project Explorer but because you only have 1 project in your solution the solution root is not being displayed.

If you click File-->New-->Project the "New Project" dialog will be displayed. This has a dropdown labelled "Solution" in which you should select "Add To Solution". This will add your new project (which can be a SSRS project) to the existing solution and the solution root will be displayed as you would hope.

Hope this helps.

-Jamie|||Ok, thanks I got it. :))

So, back to my first question. How report can be converted to MS Word format (file)?|||Reporting Services does not natively support rendering reports as MS Word Documents. This is outlined in the following Q&A for Reporting Services 2005.
http://msdn.microsoft.com/SQL/community/webcasts/ReportingServicesQandA.aspx
You'll either need to code it yourself, purchase a third party component, or change to another output format.
The easiest solution would be to change to the PDF output format.
If you really need to have the report in Word format, you could try saving the report as MHTML and renaming the extension to ".doc". I won't guarantee that the report will be formatted correctly because your essentially opening a webpage in Word. This will only really work in Word >= 2000.
You could also try using Office Automation to build the report. Without attempting it myself, I would guess that this would not be easily integrated into a Report Services Solution, since you would have to build a .Net Assembly to query the DB and build the word doc.
Larry|||Ok... Thanks.
Last question. How I can include report part in SSIS package itself? So the report can be executed on package flow. Let's say I have 2 files. I do some data manipulation and then for every file I need report.|||

Metal_Fly wrote:

Ok... Thanks.
Last question. How I can include report part in SSIS package itself? So the report can be executed on package flow. Let's say I have 2 files. I do some data manipulation and then for every file I need report.

There is currently no out-of-the-box task that allows you to do this. You could roll your own or use a script task.

-Jamie|||Ok, if I use script task, how I reference to report?|||

You can't use SSIS to push data to SSRS. So you'll need to use a temporary table to store data, and then ren the report (e.g. using script task).

What is usually done is the opposite - schedule a SSRS report and use SSIS as data source for the report. This way you can avoid any temporary table.

|||Or, for SSRS reports, one can programmatically generate the reports as .RDL files. BOL contains a summary of this approach, along with a tutorial and code. Start at "Generating Report Definition Language Programmatically."

However, to return to your original request for Word reports, you do not want to automate an Office application in an unattended package. The better solution would be to generate WordML XML files. Although a WordML file is a frightening site to behold -- and many XML editors refuse to display and format it for you because of nested and recursive entries -- the bare minimum of XML elements required is fairly manageable.

You could accomplish this by selecting your data FOR XML and putting your conversion logic in an XSLT stylesheet, or more procedurally/verbosely by writing the data out one row and column at a time by using the .NET's XmlTextWriter. I have a sample using the latter approach to load a SQL table and write it out as an Excel XML worksheet, or a table in a Word XML document. Someday in "spare time" I'd like to adapt this as a custom SSIS destination.

-Doug|||Thanks everyone. I will try to to use BOL (Generating RDL programmatically).
Keep you informed on my progress/regress. :))|||Ok, here is another thing. One option for report in Preview mode
is to export it to Adobe format (.pdf).
How I can do that programatically? In VB.NET for examp.
|||You will need to invoke the Reporting Services web service and in particular its Render method, which accepts a format argument. See the Reporting Services Programming section of BOL, almost right under the Integration Services Programming section.

If you need additional details on Reporting Services programming, you'll probably get more detailed responses in a Reporting Services newsgroup.

Off to Thanksgiving dinner!

-Doug|||Seems too complicated and there is definately lack of info how to do that
on practice. But thanks anyway.