Run an SSIS job from SSRS

An important part of being a responsible boater is to prevent the spread of invasive species. When pulling your boat out of the water, plants and animals can “hitch a ride” by attaching themselves to your boat or trailer or sitting in your boat’s livewell or bilge area. Then when you move to the next lake they will get off or out of your boat and begin reproducing. This can introduce species of plants and animals to other lakes where they don’t exist natively. The ecosystem of each lake can be delicate, and introducing a new species can severely impact the species that already thrive in the lake. As an example, zebra muscles are an invasive species that are wrecking havoc on the lakes in Southeastern Wisconsin. Zebra muscles look like tiny clams with stripes, and they attach themselves to anything (rocks, sand, plants) and filter particulates out of the water. This makes the water much clearer which impacts the predators, who will now need to find more effective ways to ambush their prey.
I was surprised to find a local lake closed due to an invasive plant species, Starry Stonewort. From what I’ve read, this plant grows so thickly that poison is ineffective in trying to destroy it. It also hardens almost like coral, and reduces the amount of area that fish can use to spawn. It is very important to check your boat and trailer after pulling out of the lake but before leaving the launch to make sure you are not carrying any aquatic hitchhikers.
SSIS is a great tool for many tasks, especially moving data into and out of SQL Server. However, this tool is not easily accessible to the majority of the members of many organizations. One good way to let these types of users move data around is to set up the SSIS packages to be run in an SSRS report. Unfortunately there are a few steps to get this set up, but once you have it up and running you can cut yourself out of a lot of simple but time consuming tasks by letting users load or extract their own data to use in Excel or anywhere else.
The first step is to create an SSIS package to do the actual movement of data. The input or output will usually have to have a standard naming convention and standard field configuration.
20150810 SSIS from SSRS SSIS package
Next, create a SQL Agent job that will run that SSIS package.
20150810 SSIS from SSRS SQL Agent Job
The next step is to create a stored procedure that will run that SQL Agent job.

-- =============================================
-- Author:		Gugg
-- Create date: 2015-08-10
-- Description:	Runs the SQL Agent job that imports the IQR files
-- =============================================
CREATE PROCEDURE [dbo].[ImportIQRFiles]

AS
BEGIN

   SET NOCOUNT ON;

   EXEC msdb.dbo.sp_start_job  
    @job_name = N'AdHoc - DirectTech IQR Import'
       
END

GO

Lastly, create the SSRS report that will use that stored procedure as a data source.
20150810 SSIS from SSRS SSRS Report
Now when the user clicks to view the report, the data will be successfully imported or exported into SQL Server.
20150810 SSIS from SSRS Report Icon
It can a good idea to make the SSIS package as robust as possible with error emails sent to those who you expect to be running the report, that way if the import or export fails, the user can figure out why (often a misnamed file or missing field) without having to get you involved.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s