Simplest way to constrain an SSIS Execute Process Task

My off the cuff thought would be that you need to turn on logging in your SSIS package and I'm assuming you have it pointed at SQL Server. I prefer to capture OnPre/PostExecute, TaskFailed and OnError events at a minimum.

Write a query that looks for packages that have begun execution but have not terminated. This will go against dbo.sysdtslog90 for 2005, dbo.sysssislog for 2008+ instances.

Depending on how you like to solve your problems, you can use that query in pure TSQL to detect and send mail via sp_send_dbmail (assuming you have it configured) or create an SSIS package that contains the logic and uses a Send Mail Task.

Now you have a tool that when it runs detects the overlong situation and sends notification.

The final step is how do you make it go. The cheap and lazy route would be to schedule a job every N intervals that fires off the solution above and checks. If you wanted more elegance, you could add a step in your existing SQL Agent job that schedules a one off execution of your job check logic, perhaps even a self-deleting job scheduled for a single execution, that runs 2:30 from now. If the package completes, then it will do nothing and delete. Otherwise, you get your page and have to jiggle the database. Harder to do it this way and uglier to test but there's a certain elegance to it.

I would advice you to upgrade the package by following these steps

If after your upgrade you still run in to problems, it could be that your package is using components/drivers that are not available on 64bit. In that case the "easiest" work around would be to have your package use a 32bit runtime.

Open de Job properties and then edit the job step that is calling the SSIS package.

Go to the Execution Options tab and check "use 32bit runtime"

It adds the switch /X86 to the Dtexec execution string. (please note that this switch will be ignored if you directly run it from the command line.)

However, be aware that in that case, you are running the package in 32bit while not making use of the 64bit benefits. This shouldn't have to be a problem, just something to keep in mind.