Geeks With Blogs

@cgroom
  • cgroom I saw a De Lorien on I55 today. Started chasing after it, but lost it at about 88 MPH... about 471 days ago
  • cgroom #passbac updatable and clustered Velocity column store now reality in #SQLserver PDW, and coming in the next release of SQL Server. about 508 days ago
  • cgroom True. RT @pietergoes: Analyze this! I can't believe the amount of presentations that focus on twitter analysis. #passbac about 509 days ago
  • cgroom #passbac wondering if Patrick the great and powerful is Patrick Baumgartner... about 509 days ago
  • cgroom #passbac "predictions are hard." Tech should support decision making, and not supplant it. 3Difft tools/approaches = 3 difft predictions... about 509 days ago
  • cgroom #passbac fun is why we do what we do. Creative play with data... about 509 days ago
  • cgroom #pasbac data scientist = data mechanic about 509 days ago

SQL Monger Solving BI challenges and more!
| Home |

It has been quite a while since I signed up for this blog site and high time that something was posted.  I have a list of topics that I will be working through and posting.  Some I am sure will have been posted by others, but I will be sticking to the technical problems and challenges that I’ve recently faced, and the solutions that worked for me.  My motto when learning something new has always been “My kingdom for an example!”, and I plan on delivering useful examples here so others can learn from my efforts, failures and successes.

A bit of background about me… My name is Clayton Groom. I am a founding Partner of the consulting firm, Covenant Technology Partners, LLC, located in St. Louis, Missouri.  I focus on SQL Server Data Warehouse design, Analysis Services and Enterprise Reporting solutions.  I have been working with SQL Server since the early nineties, when it still only ran on OS/2. I love solving puzzles and technical challenges.

Enough about me… On to a real problem…

SSIS Connection Time outs versus Command Time outs

Last week, I was working on automating the processing for a large Analysis Services cube.  I had reworked an SSIS package and script task originally posted by Vidas Matelis that automates the process of adding new and dropping old partitions to/from an Analysis Services cube.  I had the package working great, tested, and ready for deployment.  It basically performs a query against the source system to determine if there is new data in the warehouse that will require a new partition to be added to the cube, and it checks the cube to see if there are any partitions that are present that are no longer needed in a rolling 60 month window.

My client uses Tivoli for running all their production jobs, and not SQL Agent, so I had to build a command line file for Tivoli to use to run the package.

Everything was going great. I had tested the command file from my development workstation using an XML configuration file to pass in server-specific parameters into the package when executed using the DTExec utility. With all the pieces ready, I updated the dtsconfig file to point to the UAT environment and started working with the Tivoli developer to test the job.  On the first run, the job failed, and from what I could see in the SSIS log, it had failed because of a timeout. Other errors in the log made me think that perhaps the connection string had not been passed into the package correctly.

We bumped the Connection Manager  timeout values from 20 seconds to 120 seconds and tried again. The job still failed.

image

After changing the command line to use the /SET option instead of the /CONFIGFILE option, we tested again, and again failure.

After a number more failed attempts, and getting the Teradata DBA involved to monitor and see if we were connecting and failing or just failing to connect, we determined that the job was indeed connecting to the server and then disconnecting itself after 30 seconds.  This seemed odd, as we had the timeout values for the connection manager set to 180 seconds by then.  At this point one of the DBA’s found a post on the Teradata forum that had the clues to the puzzle:

There is a separate “CommandTimeout” custom property on the Data source object that may needed to be adjusted for longer running queries.  I opened up the SSIS package, opened the data flow task that generated the partition list table and right-clicked on the data source.

image

from the context menu, I selected “Show Advanced Editor” and found the property. Sure enough, it was set to 30 seconds.

image

The CommandTimeout property can also be edited in the SSIS Properties sheet.

In order to determine how long the timeout needed to be, I ran the query from the task in the development environment and received a response in a matter of seconds.  I then tried the same query against the production database and waited several minutes for a response. This did not seem to be a reasonable response time for the query involved, and indeed it wasn’t. The Teradata DBA’s adjusted the query governor settings for the service account I was testing with, and we were able to get the response back down under a minute.  Still, I set the CommandTimeout property to a much higher value in case the job was ever started during a time of high-demand on the production server.

With this change in place, the job finally completed successfully.  The lesson learned for me was two-fold:

  1. Always compare query execution times between development and production environments, and don’t assume that production will always be faster.  With higher user demands, query governors, and a whole lot more data, the execution time of even what might seem to be simple queries can vary greatly.
  2. SSIS Connection time out settings do not affect command time outs.  Connection timeouts control how long the package will wait for a response from the server before assuming the server is not available or is not responding. Command time outs control how long a task will wait for results to start being returned before deciding that the server is not responding.

Both lessons seem pretty straight forward, and I felt pretty sheepish once I finally figured out what the issue was.  To be fair though, In the 5+ years that I have been working with SSIS, I could only recall one other time where I had to set the CommandTimeout property, and that memory only resurfaced while I was penning this post.

Posted on Sunday, February 13, 2011 4:46 PM | Back to top


Comments on this post: First Foray–About timeout

# re: First Foray–About timeout
Requesting Gravatar...
Hi,
Thank you for sharing your experience...
I will admit that i don't know very much about SSIS, but i believe that I'm currently experiencing a similar issue with a SSIS package that was developed by a vendor. Basically we call the package from a .bat file via sched tasks and in the logs we are getting messages related to timeouts. I have poked my way around SSIS and found the "CommandTimeout" property that is mentioned in your article. Do you know how to set this value using dtexec? I tried adding "/SET CommandTimeout;0" to the dtexec command in my bat file, but the logs indicated that it wasn't the correct syntax:
Warning: 2012-05-14 17:14:58.47
Code: 0x80012018
Source: Incremental_CCIS_Extract_2008
Description: The configuration entry, "CommandTimeout", has an incorrect format because it does not begin with the package delimiter. Prepend "\package" to the package path.
End Warning
Warning: 2012-05-14 17:14:58.47
Code: 0x80012017
Source: Incremental_CCIS_Extract_2008
Description: The package path referenced an object that cannot be found: "CommandTimeout". This occurs when an attempt is made to resolve a package path to an object that cannot be found.
End Warning
DTExec: Could not set CommandTimeout value to 0.

I have also tried to Prepend "\package" to the package path...as the error suggests (/SET \package.CommandTimeout;0) and got the same messages in the log....so I must be doing it wrong....
Any suggestions?
Left by Dan on May 15, 2012 1:29 PM

# re: First Foray–About timeout
Requesting Gravatar...
I found a similar though separate issue in SQL Server 2012 where I got the error message:

Failed to execute IS server package because of error 0x80131904. Server: ., Package path: \My Folder\My Package.dtsx, Environment reference Id: NULL.
Description: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Source: .Net SqlClient Data Provider
Started: 1:57:04 PM
Finished: 1:57:35 PM
Elapsed: 30.311 seconds


The cause, surprisingly, was an extra space in our DTEXEC parameters. Full details are here:
http://stackoverflow.com/questions/16972536/dtexec-sql-server-2012-timeout-after-30-seconds/16972537#16972537
Left by Brent Lightsey on Jun 06, 2013 4:33 PM

Your comment:
 (will show your gravatar)
 


Copyright © SQLMonger | Powered by: GeeksWithBlogs.net | Join free