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
-- =============================================



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


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.

Finding SSIS package passwords in SQL Agent jobs

It can be a daunting experience to try fishing a new lake. We can see what the lake looks like from above the water, but below there are innumerable structures and features to consider. Last summer I fished lake Winnebago for the first time with my pop and brother. Lake Winnebago is the 21st largest natural lake in the United States, covering about 215 square miles. As we motored out from the boat launch, the only thought was “where to start?” The vast lake was stretched out before us, but since we could only see the top of the water we had to use other clues to figure out where to start fishing.
It’s tough heading into the unknown. Earlier this year I started a new job that used SQL Server Integration Services (SSIS) jobs saved in the file system as steps in various SQL Agent jobs. This job was at a small shop where documentation was not well kept. The SSIS packages were password protected, but nobody knew the password! Fortunately I was able to find the passwords stored in the SQL Agent job steps using this SQL command:

FROM    msdb.dbo.sysjobs sj
        JOIN msdb.dbo.sysjobsteps sjs ON sj.job_id = sjs.job_id
WHERE   sjs.subsystem = N'SSIS'

After running, I was able to find the /DECRYPT password part of the command, which I used to decrypt and view/edit the packages using BIDS.  In the image below, you can see the password is “ssis”.

20141229 SSIS Package Passwords

Winnebago turned out to be a fun lake to fish.  We were catching fish all day, and ended up with a good amount to take home, after throwing back all the nasty sheepshead.