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?
1 Answer
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.
-
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 ??– JoeKNov 23, 2011 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!– TezzaNov 23, 2011 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?– JoeKNov 24, 2011 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!?– TezzaNov 24, 2011 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 ?– JoeKNov 24, 2011 at 13:32