Executing a SSIS Package from SharePoint 2010
March 27, 2012 10 Comments
In this post I will talk about how you can create a SharePoint list that will kick off SSIS packages. This interface will allow certain end users to have some access to package execution; it is much cleaner than having a business user log into a database to manually kick off a job.
Creating the SQL table
The package execution history will be stored in a simple table comprising of two columns: PackageName and LastExecuted.
If the user wants to rerun a specific package they will zero out the LastExecuted column. Doing so will kick off a trigger that executes sp_start_job. Below you will find the syntax for the trigger.
For simplicity’s sake the packages that are executed by the trigger only log execution time in the PackageExecution table. That way, we can quickly check to see if the package executed correctly. In the real world, I would hope you have more complex packages than that.
Creating the External Content Type
Since it’s not very easy to execute SQL directly from SharePoint, we will leverage an external content type list. This list will allow us to edit a record in a table from which we will trigger the execution of a package.
Start by firing up SharePoint designer, if you don’t have it you can grab it here: SharePoint Designer 2010
If you don’t already have a connection to your site, click on “Open Site” and type in the address of your SharePoint site. Next, under navigation, click “External Content Types”. Under File, click “External Content Type”
Populate the Name and Display Name fields, then “Click here to discover external data sources and define operations”
If you don’t already have a connection created to your database, go ahead and create one. Browse to the table that you created earlier “Package Execution” and right click “Create All Operations”.
Click through all the steps and finalize the process. Make sure to save the external content type to the Business Data Connectivity metadata store.
Create the SharePoint list
Now that we’ve created both the SQL table and External Content Type, we can build a page to work with the data. Browse to your SharePoint site, go to “Site Actions”, “More Options…”, filter by “List” and “Data”. Select the “External List”
Give the list a clear name, and browse for the content type that you created.
Click save and you should be able to see the values stored in the table.
Executing the package
Now that we have all of our components created and hooked up, it’s time to test this guy. Browse to the SharePoint page where you placed the list and delete a “LastExecuted” value. Refresh the page, and you should see a new value!
Now mind you, this example was very simple and doesn’t serve a real purpose in the real world. With this framework though, you can develop some powerful solutions. One good example happened to me recently. I had a package fail on me during a nightly load and I had no way of restarting it. Normally I would VPN into the client, but this specific one doesn’t allow that access. Thankfully, I had access to the SharePoint site and using the above solution, I was able to quickly restart the package without bothering any of the Tier 3 support guys.
|Learn more about our Dallas office||Learn more about Slalom Consulting Strategy|
Subscribe to follow new Business Intelligence posts