Paremeters into RS Report from an SQL Table

Question

Paremeters into RS Report from an SQL Table

aown61

Grasshopper

Points: 17
More actions
December 14, 2010 at 3:00 am

#229580

Hi all,
Im hoping someone here may be able to help with my query.
A little background..
I have a RSreport, which is passed a variable @GetOD (which is a name)
at the moment the report is passed this variable from abit of asp.
Once the report has been generated, it is saved to a directory on a shared drive.
This report is ran about 50 times (There are 50 different people) this is all ran manually.
What I want to do is pass the variable @GetOD and also specify the users directory on the shared drive to save the report to.
I was thinking I maybe able to do this with some kind of foreach loop container within an SSIS package. Please correct me if im totally off the mark here.
The aim here is to have this all run by itself by running a dtsx package.
If anyone has any hints or tips or a step by step, it would be greatly appreciated.
Many thanks
Andy J

Viewing 6 posts - 1 through 5 (of 5 total)

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

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 1

I would probably investigate a data driven subscription in the Report manager before trying to build an SSIS package to solve this problem.

Doug Lane SSCrazy Points: 2118 More actions · Answer 2

A data-driven subscription would be a lot easier if you could get Enterprise Edition. If that's not an option, you can do a foreach loop in SSIS just like you've described. I'm running something like that now because I'm using 2008 Standard Edition. One drawback you'll find is that DDS runs reports in parallel, whereas an SSIS package has to run one at a time (or rather, one per foreach loop). If you don't care about the whole process taking longer, that's the way to go. The basic setup goes like this:

1) Execute SQL Task: Query table with the filename, path, and report parameters (one per column) for each report iteration. Store those results in object-typed user variable.

2) In a Foreach Loop, use the Foreach ADO Enumerator, and the source variable will be the user variable from step 1.

3) Under "Variable Mappings", map the output columns of the query to other user variables for filename, path, and other report parameters.

4) Run and save the report using a Script Task, calling the variables to set the filename, path, and any other parameters.

This is the simplest and quickest [to build] solution I've found using SSIS.

aown61 Grasshopper Points: 17 More actions · Answer 3

Hi Doug,

Thanks for your reply.

I have managed to sort my problem using a data driven subscription. Thanks for taking the time to reply though, it is always useful to see 'another' way of doing things also.

Many thanks

Andy

Doug Lane SSCrazy Points: 2118 More actions · Answer 4

Hi Andy,

I'm curious, do you have Enterprise Edition or did you do some other programming workaround for your DDS?

Thanks,

Doug

aown61 Grasshopper Points: 17 More actions · Answer 5

Hey Doug,

We're lucky enough to have enterprise edition 🙂

So i just have a straight forward table query to pull names, folder paths, and dynamic filename and pass these into the subscription.

SELECT OD_Desc, '\\*-*-*-*-*\d$\bi\'+Name AS Filepath,

'GOSH BDM Summary – Week '+

(SELECT RIGHT(WEEK,2)

FROM *-*-*-*-*-*-*.dbo.LABTAK_WEEK_VARS

WHERE Display='Last Week') AS DocumentName

FROM SEC_USER_NAMES

I have used the for each loop container for another process of emailing a list of people with an attachment which in comparison to the data driven sub is a little trickier.

Cheers,

Andy