Ive got an issue running an SSIS package from a Adobe AIR application. I'm not quite sure where the issue lies so I hope it's obvious to people with knowledge in the correct area!
Firstly a little on my architecture (if you can call it that).
I have an Adobe AIR application . This application uses a classic asp page to set up a connection to a sqlserver db. It then passes the string to the db. Any responses come back to the Air app via ASP page, which wraps the data up as an xml message. This works fine for most things I've had to throw at over the last year or so, such as Selects, Inserts, Exec of stored procedures etc.
I now want the user to be able to execute an SSIS package. The way I am handling this is to have a stored proc 'RunPopulateRPTSchema'. This is then run by clicking a button on the Air web application. In the Procedure is a call to a SQL Agent which runs the package: EXEC msdb..sp_start_job N'PBMIPopulateRPTSchema'
I have some processing to ensure that the proc is running, and loops till it finishes, and on finishing there is a select of how many rows have been inserted. This is what should be returned to the user to define success. My problem is that this message doesn't get returned to ASP/AIR and eventually it times out.
I put a trace on in Sql Server Profiler... It tells me that IIS aborts the process and I assume this is why i get no response. I'm not sure if there is something I should be setting to allow this!
If i run this thru Sql Management Studio, with the same Id it all works fine - no aborts. it seems a but odd, but any ideas? I think it's a timeout issue on IIS but I cannot be sure, and I'm not sure where to reset it.
One other bit of info - the package completes thru SSIS and the data gets updated, but I'm not getting that info in my application
Cheers
Andrew
sorted -
in my asp script I needed to set the CommandTimeout AND ScriptTimeout - I'd only set Server.ScriptTimeout. Apparently CommandTimeout defaults to 30 seconds.
conn.Open connString
conn.CommandTimeout = n seconds
Server.ScriptTimeout= n seconds
I'm still really kean to make this process better so I'm eager to hear any improvements.