SSIS SQL Task Query Timeout

vzorn
vzorn used Ask the Experts™
on
I need a solution for the error I am getting in my SSIS package.
For the SQL Execute Task I am running a stored procedure.
If it takes longer than 30 seconds the task fails.
I have already tried splitting the process into four tasks but it fails about every other time.
I have read there is no fix for this because the timeout option does not work.

I need some other solution for how to run my project.  I am querying data to pull into an XML file and then ftp the file.

ERROR is
 failed with the following error: "Query timeout expired".
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Consultant, Trainer
Commented:
try to change your connection manager to overcome the error of timeout.
I mean if your connection is OLE DB , change it to ADO or reverse.

Author

Commented:
I had to use ADO because the OLE DB would not let me run a stored procedure.  It disables the option for stored procedure.
Reza RadConsultant, Trainer

Commented:
you can run stored procedure with OLE DB too,
just use this syntax as your sql statment:
exec yourSPName ?,?,?
which question marks are parameters
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
Ok, I will try that in the morning and get back to you.
Reza RadConsultant, Trainer

Commented:
I checked this with a longtime sp and with OLE DB connection, and this works correctly , no error while 2 minute of executing.

Author

Commented:
It worked perfect.  Both processes took over a minute and they ran without an error.

Thank you so much!!
Reza RadConsultant, Trainer

Commented:
your welcome,
note that there is an SSIS zone in experts-exchange, I probably am there, if you want to get answer of your question about ssis fastly, it's better to put your questions in that zone.
Regards,
Reza Rad
Reza RadConsultant, Trainer

Commented:
please close this question if your problem solved
Regards,

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial