August 2, 2011 at 1:36 am
Hi
I need some help, i have built an SSIS package which is designed to run on a production server remotely using the DTEXEC utility. The package hangs indefinitely at validation stage and there will be no progress, until the SERVER is REBOOTED.
The package is a stand-alone and uses configuration file to feed the necessary inputs to the package at runtime. The package has following components,
1. Two connection managers
2. User defined variables (declared at package level)
The inputs used in configuration file are
a. Connection strings for two connection managers (One for SQL server and one for MS-ACCESS)
b. Insert statements which contains values to be inserted through Execute SQL task
c. Command line arguments as input to Process tasks
It is to be noted that this package works fine, when the server is rebooted, but does not work when other processes have run on the production server prior to running the SSIS package (these processes are run at different point in time and not parallel to the SSIS package execution).
Can anyone help me what is going wrong with the package or have i missed to turn on any other property which is making the package hang at validation stage itself AT TIMES.
Any help is much appreciated...
August 2, 2011 at 4:05 am
Maybe one of the databases is sometimes offline and there is no time-out set on the connection?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 2, 2011 at 5:00 am
Hi
Thanks for the reply.
But i think that would not be possible as the database will be constantly referred by other processes during the month. The fact that makes me to think and the DBA team, is why a reboot solves the package hanging issue and not any other work around. Issuing a PROD server reboot is too costly in terms of business impact. Thats why i'm trying to figure out, if there is something missing in my package.
August 2, 2011 at 5:12 am
What happens when you open the package in BIDS? Does the validation succeed?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 2, 2011 at 5:14 am
Correct. it works fine when open the same package through BIDS.
August 2, 2011 at 5:16 am
splinter-740692 (8/2/2011)
Correct. it works fine when open the same package through BIDS.
In BIDS, do you use the same configuration as on the server?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 2, 2011 at 5:18 am
Yes, i use the same dtsconfig file, referred through package configuration option in SSIS menu
August 2, 2011 at 5:21 am
splinter-740692 (8/2/2011)
Yes, i use the same dtsconfig file, referred through package configuration option in SSIS menu
OK. How does the package run on the server? Is it started by a SQL Server Agent Job? Or directly with DTEXEC?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 2, 2011 at 5:28 am
The package is called using a parameterized stored procedure which will execute the DTEXEC utility via XP_CMDSHELL
Note: The user account has full privileges (SYSADMIN) on the PROD server. So permission issues should be ruled out.
August 2, 2011 at 5:45 am
splinter-740692 (8/2/2011)
The package is called using a parameterized stored procedure which will execute the DTEXEC utility via XP_CMDSHELLNote: The user account has full privileges (SYSADMIN) on the PROD server. So permission issues should be ruled out.
Dammit, that was my next question 🙂
All I can think of now is that something else has placed a lock on the ACCESS database, so that SSIS has to wait.
Rebooting the server removes the lock so the package will work again. What do you think?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 2, 2011 at 6:18 am
Thank you Koen.
Absolutely logical!!!
But my next thought is, no ordinary user can work with the MS-ACCESS database. its a locked down environment and only the user account has full privileges to play with the in-bound files.
Also, when the server has a reboot, i am able to execute the SSIS package multiple times without getting it hanged at validation stage. Could there be a reason behind it as well?
August 2, 2011 at 6:58 am
Well, I don't know your environment of course, but you mentioned that the package only hangs when other processes have been running prior to the SSIS package. Maybe some of those processes lock something?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply