Final note on creating PDF using SSRS and SSIS

Business Scenario:

The client gets some kind of feeds that writes into a process table of their client processing system.  The data is stored directly as the raw XML file inside a SQL data column.  The XML could store multiple enrollment information for the same member.  We need to use this info to determine whether a member is eligible, then insert into a staging table.

The staging table is then used as a feed to drive a report that generates an image for each claim and sent off to the different provider.

Some of the staging table info need to go out to another partner.

 

Solution:

We used this (this) to extract the info out of the XML data to put them into the staging table.

We select some rows from the staging table and pass the claim ID to a script component to call the report URL to generate the images (TIFF)

We used this (this) to output the info to another XML file format for BizTalk to pick up and send info to trading partner.

 

Tips:

We have the report server set up in SharePoint integrated mode.  The trick is to go into the SharePoint Administration site and find where the site is pointing to as the Report Server.  Once you find that, then the report URL to call is just:

[Report Server location in SharePoint Administration Site/ Integration settings]?[full path (starting with http) to the report RDL including the extension]&rs:Command=Render&rs:Format=PDF&ClaimID=[whatever claim ID that is passed in]

If you want TIFF as output, then use rs:Format=IMAGE

 

Benefit:

  • The client can stick to MS SQL Standard edition.  This represents a savings of hundreds of thousands of dollars!
  • There is really no good way to pass in a dynamically generated value to a variable in a RS report.
  • We can schedule the report to run in the off business hours.

 

Related posts:

 

Print | posted on Tuesday, December 8, 2009 2:19 PM

Feedback

No comments posted yet.

Your comment:





 

Copyright © Kevin Shyr

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski