Executing a SSIS Package from SharePoint 2010

Marek Koenig specializes in Business Intelligence, SharePoint and Custom Development.

Marek Koenig

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.

The table

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.

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”

External Content Type

Populate the Name and Display Name fields, then “Click here to discover external data sources and define operations”

Discover data source

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”.

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”

External List

Give the list a clear name, and browse for the content type that you created.

Create the list

Click save and you should be able to see the values stored in the table.

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.

Happy coding!

Slalom Consulting’s Dallas office Slalom Consulting's Strategy focus
Learn more about our Dallas office Learn more about Slalom Consulting Strategy

subscribe by emailSubscribe to follow new Business Intelligence posts

About marek koenig
Microsoft consultant specializing in BI, SharePoint and custom development.

7 Responses to Executing a SSIS Package from SharePoint 2010

  1. Pingback: Executing a SSIS Package from SharePoint 2010 « The Slalom Blog | Mastering Sharepoint

  2. Juliana says:

    Hi – This is exactly I would like to implement in our system (Execute SSIS package from SharePoint). However, I missed one major point in your trigger code “Select bla..bla.. from INSERTED”. What do you mean by INSERTED here?
    Thanks in advance,
    JKoe

  3. marek koenig says:

    INSERTED is one of the in memory tables that is created by SQL Server when you use an index. The other one is DELETED. Whenever you make a change to a table that has a trigger attached to it, the results (all columns in the source table) are stored in one of these tables. That result is then used to execute the start job task.

    Read more here: http://technet.microsoft.com/en-us/library/ms191300.aspx

  4. lindellin says:

    Where should the package be physically located for this to work? E.g. if in c:\packages\mypackage1.dtsx, how do this trigger know where to look for it?

  5. Stefano says:

    Hi, maybe this post as a iittle bit old and meanwhile a better solution has been developed, but googling the topic I haven’t found nothing else.
    I tried your implementation to launch a SSIS package (or bettere a SQL job) from a sharepoint list. I followed your explanation, creating the trigger and the external list on SP.
    The problem is that after deleting the date from LastExecuted field SP is giving me back an error, whicch tells: “Impossible updating the external list entry. The query executed on the db caused an error.”

    I tried a lot of workaround without success, do you have an idea to solve this problem?

    I thank you in advance hoping for answer.

    Greet.

    Stefano Bruni

    • marek koenig says:

      Stefano,

      Are you deleting the item from the list directly or from the underlying SQL table? I would recommend checking the BDC settings to ensure that you have delete actions enabled.

      Secondly, is there a reason that you are trying to delete the record in the first place? The trigger on the table is expecting updates only, and deletes should not be necessary.

      Thanks,

      Marek Koenig

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

Follow

Get every new post delivered to your Inbox.

Join 126 other followers

%d bloggers like this: