SSIS creates a PDF file with output

Question

SSIS creates a PDF file with output

mw_sql_developer

SSCoach

Points: 19445
More actions
June 2, 2011 at 12:21 pm

#143934

Hello:
I am moderately skilled with using SSIS. Know how to
create a package and run. So far managed to save the
output in flat files. now I need to advance to the next step.
Question: I need the query output to go to a PDF file.
(Assume that the PDF file lands in some directory ).
Any help ?:-D

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

Considering PDF is for a human readable format I'm a little unsure why SSIS would be handling the formatting instead of something like SSRS. Are you using the right tool for the right job?

CEWII

mw_sql_developer SSCoach Points: 19445 More actions · Answer 2

Dont worry about the format.

All that I need is the method of getting this done.

If SSIS could save data to 1.) Flat files, 2.) Excel then

there should be a way to save it to PDF as well ?

mw_sql_developer SSCoach Points: 19445 More actions · Answer 3

Hello:

I certainly would not mind building the report in SSRS.

However, what iI really need is this report be generated

automatically ( at a given time in the night ) and the PDF file

should be saved to a directory.

Hope I explained what I need clearly ?

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 4

you should be able to do this with some out of the box SSRS functionality (ie from the URL it can be called to save a report to PDF; you can schedule reports;) plus a little of the sample code they have (ie send to printer; or in this case, send to UNC share).

Steve.

mw_sql_developer SSCoach Points: 19445 More actions · Answer 5

Not clear what you mean ?

Are you saying

1. Design Report in SSRS

2. Deploy Report via a scheduled job in the night.

3. Expect the report to be saved as a PDF to some

directory.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 6

Yeah, basically that.

Design in SSRS, you can obviously test this easily in debug/dev mode. Publish to SSRS service and test.

It's been a while but 'back in the day', one of the coding samples for SSRS was writing a delivery extension that writes the report output (can't remember format, let's say it was excel) to a UNC path (ie directory). You would likely want/need to mod this slightly to change the rendering format to PDF from Excel (not hard to do at all). Then finally, schedule the report using SSRS Report Manager UI, choose the new delivery extension (UNC path) and test that the scheduled report renders to path as expected.

HTH,

Steve.

mw_sql_developer SSCoach Points: 19445 More actions · Answer 7

Sorry

I don't think this will work.

I forgot to make myself clear on what I need.

1.) I want the SSIS job to create more than one PDF file.

2.) Each file will have to be named uniquely ( I will figure this out ).

3.) Some of the files will have to be saved in different directories.

Think of it this way: A process that runs in the night and creates your

monthly credit card bill, saves as a PDf and then emails to each customer.

What you suggested would have been ideal if it was just one

report ( I mean running a SSRS report via SSIS )

Waht do you think ?

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 8

I think you're talking about a data driven SSRS subscription.... 🙂

Steve.

mw_sql_developer SSCoach Points: 19445 More actions · Answer 9

So you think this still can be done even if used SSRS to build the report ?

Can you lead me to some web site or book that has the stuff you are talking about ? I wish you could point me to some example.

mw_sql_developer SSCoach Points: 19445 More actions · Answer 10

Thanks

I found an example in the web. This is a tutorial.

Lets see how it goes.

http://msdn.microsoft.com/en-us/library/ms169673(v=SQL.90).aspx

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 11

SHowing my 'age' since reading the docs.... Back on 2000 and maybe 2005, the file share was a sample :hehe:

So, now in 2008, it's standard - doc here.

This should get you started on subscriptions. Not eit also covers email delivery (which may be what you want too/instead). I am not sure if that page covers data driven subscriptions, and you may want to check your Edition of SQL/SSRS, as this *could* be an Enterprise feature - again, not sure what you're running.

HTH,

Steve.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 12

Data driven subscriptions IS an enterprise edition feature. However, this is very different than a DDS, the values are set at design-time and don't have to change like they would in a DDS..

CEWII