February 8, 2011 at 8:54 am
I am having an issue with a 2008R2 SSIS package the loads a Datamart. The packages are nested, as I have one package calling sub packages.
I can execute the package from BIDS and everything will work. However when I execute from SQLAgent, the package appears to stop working. No errors are reported and the SQL Job still shows that it is executing. I had the SQL Job simply executing a bat file with the dtexec call with the correct configuration file. Then I changed the SQL Job to execute the package stored on the file system directly.
The package enters logging rows into a couple of tables, so I can monitor execution and performance. At some point it just stops doing anything, it does not error out, write a logging row or have any activity. I have let it run for over 24 hours before stopping it.
Does anyone know how I can find out what is going on?
Thank you.
February 8, 2011 at 9:03 am
I have had something siimilar happen to me, it turned out to be a modal dialogue box that was been raised by a script component. This would casue the package to sit and wait until someone clicked 'ok' which was not going to happen in an agent task.
You could also try and run a server-side trace to see what, if anything, the package is doing.
February 8, 2011 at 2:38 pm
How did you work around the issue of waiting on a modal window?
February 9, 2011 at 4:41 am
I'm having a simliar issue also with 2008R2 SSIS.
master package that calls 6 children (in series) to load data into an oracle db using attunity drivers.
At random intervals one of the 6 (no pattern to which one) will just stop doing anything. No errors reported. no termination of package. just sits there. Don't think its a modal popup problem as the packages are purely, ole source (sql server on same box as running ssis packages) that load to oracle tables on a remote server, with little or no transforms on the way.
I've created warning reports for the IT ops team that goes all red and flaming if the package hasn't completed in a reasonable time. If the problem occurs the only way to resolve is to terminate job, delete any of the data that did reach destination then start the job again. Sometimes they have to do this up to 3 or 4 times (but sometimes just once) to get it to work. Then it will run fine for what seems a completely random amount of times, then problem arises again.
No idea how to troublshoot this!
Michael.french If you do manage to resolve please post, as IT ops are really bugging me about this now...
February 9, 2011 at 4:47 am
michael.french 172 (2/8/2011)
How did you work around the issue of waiting on a modal window?
I fixed the error that was causing the modal window, it was a datatype conversion error that would only happen Intermittently.
do you have any script tasks in the package ?
February 9, 2011 at 8:06 am
SteveB,
Yes, every package has many script tasks for logging and statistics capture. The error window is something that I may look at. Everytime I open a package, I get an error complaining about connection strings not matching. I had never worried about that much because I am using a config file and they work. Like nahk.fussuy, there is no rhyme or reason as to when or why the package execution just sits and waits.
nahk.fussuy,
Glad to hear that I am not the only one with this issue. I will post what I find. My client is not happy with this situation either.
Michael
February 23, 2011 at 2:16 pm
As it turns out, it was an issue with connection pooling with OLE DB connections.
I was able to capture the following error:
TCP Provider: Only one usage of each socket address (protocol/network address/port) is normally permitted.
The App Dev that I was working with suggest the pooling was not configured correct. After some research, I found where to configure the option. It was in the data source definition and not the actual package definition. I base all of my packages off a common set of data source and then dynamically configure each string based upon environments.
When the data source is configured, there is an option in the All tab of the Connection Manager. It gave pooling options, so I select ResourcePooling instead of Default. After that, everything seemed to work.
February 26, 2011 at 6:32 am
michael.french 172 (2/23/2011)
What type of data source is this? I've had a similar issue with a pervasive data source.
March 1, 2011 at 7:54 am
michael.french 172 (2/23/2011)
When the data source is configured, there is an option in the All tab of the Connection Manager. It gave pooling options, so I select ResourcePooling instead of Default. After that, everything seemed to work.
Interesting solution Michael! So the problem seems to be on reading the source, and not the insert at destination? Hope thats it as I wouldn't know where to start with configuring resource pooling for the oracle side! (to the google machine!!)
I've discovered that setting the Pooling to 'ResourcePooling' as you described above just adds the following to the connection string property: OLE DB Services=1;
As I use a SQL server config table to get the source server connectionstring, I'll get it updated to add this item in. I'll post back if the problem gets any better or worse!
March 1, 2011 at 12:09 pm
The effects of setting that option are consistent with what I saw. My data sources were also configured from a table and that is how it was easily able to fix the issue for all packages.
Categorizing the problem as a source or destination is a little bit of a misnomer. The problem really refers to the local vs. remote server definition in the data source. In my case, the source database was on a different server than the SSIS package and the destination SQL Server. It is interesting to note that this behavior does not show up when both source and destination are on the same server. That explains why it works in development and then encounters issues when you get to production.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply