Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

The scenario is that Excel files are uploaded to a folder in SP2010. When a new file is loaded, I would like to use a Workflow to call the SSIS package that processes that file into a Database and updates an SSAS Cube. I cannot see any action in the Workflow tasks or actions that will call an external process, either Powershell, win32 or Cmd ? If it is not in Workflow surely there must be some approach in Sharepoint to call an external process, to enable Interfacing?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

What is the workflow doing? If its doing nothing but calling ssis then it's a little overkill since its workflowing nothing!

Why not have the ssis package run under a SQL job periodically checking for new files?

If it needs to run on upload (don't forget to cater for check out/in), an option is to call a stored procedure to execute the ssis package using xp_cmdshell. Alternatively use sp_start_job and start a job configured to run the package.

I'd use one of the above given its on a remote server? If local you could use Microsoft.SqlServer.ManagedDTS

Hope it helps.

share|improve this answer
    
Thanks for the reply @Tezza. I can indeed schedule the task to run every x mins from the SQL environment, but this will load the server and there will only be files to work on every few days or so, most of the time. I cannot believe that there is no way to initiate an event/process outside SP ?? –  JoeK Nov 23 '11 at 0:34
    
Can you clarify what you mean by outside of SP? Calling a SQL proc whether from workflow, event, etc is outside of SP. After all its SQL starting the package! –  Tezza Nov 23 '11 at 7:50
    
I wanted to take an event based approach by getting the file upload event "calling" (somehow) the SSIS package. I am going to schedule the package with SQL Agent to run every 5 mins or so, but it would be a better solution if I can get the package to run only when it is needed? –  JoeK Nov 24 '11 at 9:52
    
So why not do as described? Have a list event on the document library in SharePoint. This list event then OnAdded / OnCheckedIn fires a SQL command to the SQL Server? Using xp_cmdshell you could execute the package directly or use the existing proc sp_start_job to start a SQL job configured to run the package. Yes, there is a SQL job in this instance but with NO schedule it will only ever run when instructed to do so. This would be then event driven execution of the SSIS package!? –  Tezza Nov 24 '11 at 11:49
    
Ahhh, the missing nugget ;-)) What is a List Event and how do I go about such a thing ?? Interesting that this is the first time I have heard of such an animal? Are they not often used ? –  JoeK Nov 24 '11 at 13:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.