October 17, 2011 at 1:40 pm
When trying to execute a SSIS package...
It works fine when I run it from inside BIDS, but when trying to automate it by creating a job on SQL Server, when it is time to run it, it hangs and just says its executing...But when run in BIDS, it only takes less than a minute for it to complete...Not sure what is going on here...Here is what the package does:
1. Execute Process Task: Runs a batch job
2. Script Task: Gets the latest file
3. Data Flow Task: Loads flat file into a recordset
4. ForEachLoop Container that iterates for each row in the recordset...Within the container is another Execute Process Task that runs an .exe
5. Script Task: Archives the file
Like I said, from BIDS, I can run this over and over and it will complete everytime, all I have to do is click Start...However when I tried to set this up as a job and schedule it, it just sits there and gets hung trying to do something...I cant go into the package and see what it is hanging on (at least I dont know how if you can) so I dont know how to solve this issue...Does anyone have any suggestions for me to look at?
Thanks in advance...
October 17, 2011 at 2:09 pm
When you run it in BIDS, are you running it with the same account that runs it in the job?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 17, 2011 at 2:15 pm
Yes...and it has administrative privileges...
October 17, 2011 at 2:20 pm
Are you running BIDS on the same server as you have scheduled the job?
October 17, 2011 at 2:32 pm
Yes...I created the package on Server A and created the job to point to the package on Server A...
October 17, 2011 at 2:34 pm
.. And the job runs on server A too?
October 17, 2011 at 2:38 pm
Yep
October 17, 2011 at 2:42 pm
And you get no error message - the job just spins?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 17, 2011 at 2:46 pm
Yeah no failure, no success...The job is scheduled to run at 8 am...And if it fails, I get notified via email...This was the first day it was scheduled to run...So at 815 I realized I havent received an email saying the job had failed, and I checked the db to see if any updates had been made and they had not been...So I went to SQL Server Agent, clicked Job Activity Monitor and it was saying it was still executing...And I knew it didnt take 15 min to run the package...So I stopped the job and tried starting it again and the same thing happened...It was like it was hanging on something...So I finally just stopped it and went into BIDS and ran the package manually and poof, it ran successfully in under a minute!
Idk what is going on!
October 17, 2011 at 2:49 pm
For grins - export the package from your server and execute that exported package from bids. Also, verify the configurations, command line, variables and so forth that are in the job.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 18, 2011 at 6:35 am
The one place where I could see it hanging for some reason, without throwing an error, would be at the execute process task.
If you're executing a process and for whatever reason the process fails to terminate, the SSIS package will wait on the process, and thus seemingly hang.
Have you tried executing the task from your SQL Server Agent, and then looking at the task manager on the machine and seeing the processes running? Might give you some insights.
October 18, 2011 at 9:48 am
Ok here is what I did to narrow down the causes...
I set up a TEST package and disabled the first Execute Process Task...And scheduled the package to run and it ran successfully...
So why now does it hang on the Execute Process Task...All it does is call a batch file and runs the batch file...
Why does that work when I run it manually but not when I schedule it to run? Is there a way to fix this?
When I do run it manually I dont have to anything except click Start and the package will go thru each step and complete each step successfully...
October 18, 2011 at 10:08 am
Does the batch file require any sort of interaction? Maybe it doesn't close at the end of it, and you manually close it?
Make sure the batch file is not ending with a pause or something that would require you to close it.
October 18, 2011 at 10:23 am
I am not that familiar with batch files...But the last line on the batch file calls a .dat file and the .dat file ends with BYE on the last line...
I dont have to close anything out when I run it manually...does that matter?
Also after doing some more research, I saw where one guy put SET SEE_MASK_NOZONECHECKS=1 at the top of the batch file...Does anyone know anything about that command? Will that work?
October 18, 2011 at 11:07 am
Ok guys...First of all, I would like to thank all of you who chimed in with your opinions on how to resolve this issue...
It seems that when I put that SET SEE_MASK_NOZONECHECKS=1 in the batch file, it worked...I set up an exact replica of my package on my local machine to test it and I first ran it manually and it ran successfully...Then I scheduled it as a job in SSMS and ran the job and it also ran successfully...Now all I have to do is wait for it to run on production in the morning to see if runs...But that seemed to have fixed the issue!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply