October 7, 2016 at 5:48 am
Hi
I have various packages which checks if a file is in a directory and if found kicks off the rest of the package.
If I run the package in dev studio it completes the job in 0.17 seconds. The same job in Job Agent will take between 30 and 60 seconds.
The once package has 50 + steps and each on of them takes 30 to 60 seconds to complete. This is waste time where I can use this 30 - 45 mins the package runs to actually get to the last step where I create my summary tables.
I'm using SQL server 2014, dont know the SP but Im sure its the latest, but this is really starting to waste my time. I've checked on all other forums and the suggestions but most are from people with no clue asking if the person has cursors in the package, SP's etc etc. Mine is totally straight forward, but it seems that the job agent wants to "compile" the package or validate it first and then run it. Its not the package self its the JA and the way it kicks off the package.
Any ideas?
Many thanks
October 11, 2016 at 12:06 am
chris.stuart (10/7/2016)
HiI have various packages which checks if a file is in a directory and if found kicks off the rest of the package.
If I run the package in dev studio it completes the job in 0.17 seconds. The same job in Job Agent will take between 30 and 60 seconds.
The once package has 50 + steps and each on of them takes 30 to 60 seconds to complete. This is waste time where I can use this 30 - 45 mins the package runs to actually get to the last step where I create my summary tables.
I'm using SQL server 2014, dont know the SP but Im sure its the latest, but this is really starting to waste my time. I've checked on all other forums and the suggestions but most are from people with no clue asking if the person has cursors in the package, SP's etc etc. Mine is totally straight forward, but it seems that the job agent wants to "compile" the package or validate it first and then run it. Its not the package self its the JA and the way it kicks off the package.
Any ideas?
Many thanks
I've run into this a couple of times and I'll outline what I found....
Firstly, there is this known bug (which is still not fixed in 2014 according to the comments in the connect article): https://connect.microsoft.com/SQLServer/feedback/details/783291/ssis-package-fails-to-start-application-lock-timeout-in-ssisdb-catalog-create-execution#tabs
I know this bug in particular relates to timeouts but the core issue is that the catalog.start_execution SP is slow in some cases. In my case the "problem" was with concurrency. So by staggering packages slightly it resolved it.
Secondly, because you are accessing the file system with this package, check the permissions of the account running the agent job. In my case I had a package that was slow to start because the proxy account did not have elevated permissions in the directory. Changing this resolved it. I'm no expert in security so have no idea why this would happen but I just know that it worked.
Lastly, accusing people that have tried to help you on other forums as having "no clue" hardly entices people here to answer your question, which is probably why it has been sat here for a few days. They're just trying to help. Please try my suggestions but if you think I've been unhelpful or have "no clue" then please come back and be polite and let the people here try to get you to a resolution. Alternatively, you can call me clueless and I'd be happy to suggest some very uncomfortable places where you can stick your issue 😛
October 11, 2016 at 12:24 am
Hi PB_BI
Thanks for the response, at least you have given me some real world suggestions that I can have a look at.
Im not sure how to check the start_execution SP, but this is really something I would look at as it seems that the package is running fine once its running, but it takes a long time to start the package itself.
As for the bug, well, its really sad that MS would still release a product with a bug in, but they make no effort in fixing it. But then again, I stuggle with SSRS everyday as well, and we all know how buggy than can be as well.:-D
The clueless statement I made is from other websites I visited and most certainly not from this forum. I prefer to see if I cant find a post which might solve it, but all of the responses were, how to I say, clueless. I refer to those that would ask silly little questions about have you rebooted the server or are you sure you dont access file on a different server etc. I just need to sort out the rather noob responses from the professional ones. 😉
October 11, 2016 at 12:31 am
chris.stuart (10/11/2016)
Hi PB_BIThanks for the response, at least you have given me some real world suggestions that I can have a look at.
Im not sure how to check the start_execution SP, but this is really something I would look at as it seems that the package is running fine once its running, but it takes a long time to start the package itself.
As for the bug, well, its really sad that MS would still release a product with a bug in, but they make no effort in fixing it. But then again, I stuggle with SSRS everyday as well, and we all know how buggy than can be as well.:-D
The clueless statement I made is from other websites I visited and most certainly not from this forum. I prefer to see if I cant find a post which might solve it, but all of the responses were, how to I say, clueless. I refer to those that would ask silly little questions about have you rebooted the server or are you sure you dont access file on a different server etc. I just need to sort out the rather noob responses from the professional ones. 😉
Fair enough, but have you rebooted the server? 😀
Let us know how you get on....
October 11, 2016 at 1:24 am
Something I totally forgot to add is that the SSIS packages are stored in a file directory on the server and not in the SQL Server SSIS instance. Maybe not ideal, but this is how I got the system and previously on 2008 it worked perfectly and it executed in seconds as opposed to 2014 where it takes 30-50 seconds.
And I'll give it a boot, thanks
October 11, 2016 at 2:12 am
chris.stuart (10/11/2016)
Something I totally forgot to add is that the SSIS packages are stored in a file directory on the server and not in the SQL Server SSIS instance. Maybe not ideal, but this is how I got the system and previously on 2008 it worked perfectly and it executed in seconds as opposed to 2014 where it takes 30-50 seconds.And I'll give it a boot, thanks
Ah okay, that does change things quite a bit and the upgrade is a pretty big piece of information that we need to know.
This means that the agent is using dtexec directly to run your package. In the agent step if you go to the command line tab you should be able to grab the command that it's running. Try to run that under your credentials on the server from a cmd window and see if you get the same lag. If you do (and assuming the agent job isn't running under your credentials *and* assuming that the speed you are seeing in SSDT is achieved running under your credentials) see the same lag then the permissions issue is out.
If so take, make sure you turn on logging (as verbose as you think you'll need) and then you can look at the log and see what exactly in the package is taking time. If this issue has only just started occurring on 2014 after upgrading the packages then it could be the package itself, in spite of what you have seen previously. Does it have a script task by any chance?
Failing that, deploy the package to the SSISDB catalog and execute them that way. It's much easier to diagnose the problem using the logging from there.
October 11, 2016 at 2:40 am
Many thanks, I'll look into this a little later during the week.
I've grown so accustomed to the fact thats on the file system that it didnt even occur to me to mention it.:w00t:
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply