( Part 2 focuses on clearing out the SSISDB by creating new stored proc based on the one used by the maintenance job. If your Maintenance Job is taking hours to run, you need to check this out.)

When deploying our Ispacs through my builds, I've still been getting the intermittent “timeout” issues I first wrote about back in May, and this has been exacerbated by extra SSIS Projects added to the SSISDB catalog.

Timeout expired.The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.SqlServer.IntegrationServices.Build.DeployProjectToCatalogTask.Execute()

2013-11-04-15_33_56-microsoft-sql-server-community-samples_-integration-services-source-code-com_png

sproc1_png Looking through the code in the custom task I noticed that there was no timeout override set for SqlCommand. The default timeout is 30 seconds, which is usually plenty enough, but by increasing this timeout by this using the CommandTimeout Property. This property gets or sets the length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error. So I added the CommandTimeout property and increased the value to 300 seconds (and if it did timeout after that then I knew that there'd be a serious problem). I've been running automated builds for a few days now and it looks like I've finally resolved the issue. I've posted my updated code here, so if you use the sample to deploy via automation and experience the same issue then you can take this code and add it to a Custom MSBuild Task.


using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using Microsoft.Build.Framework;
using Microsoft.Build.Utilities;

namespace Microsoft.SqlServer.IntegrationServices.Build
{
	/// <summary>
	/// This Task connects to an SSIS Catalog and deploys the given project files.
	/// Ensure that the account running MSBuild has permission to deploy to the catalog.
	/// </summary>
	public class DeployProjectToCatalogTask : Task
	{
		/// <summary>
		/// One or more paths to .ispac deployment files.
		/// </summary>
		[Required]
		public ITaskItem[] DeploymentFile { get; set; }

		/// <summary>
		/// The SQL instance name of the SSIS Catalog to deploy to.
		/// </summary>
		[Required]
		public string Instance { get; set; }

		/// <summary>
		/// The folder on the catalog to deploy to.
		/// If this folder does not exist, it will be created if <see cref="CreateFolder"/> is true.
		/// </summary>
		[Required]
		public string Folder { get; set; }

		/// <summary>
		/// Should the SSIS Catalog Folder be created if it is not already there.
		/// This property is optional. The default value is true.
		/// </summary>
		public bool CreateFolder { get; set; }

/// <summary>
/// Gets or sets the wait time before terminating the attempt to execute a command and generating an error.
/// </summary>

public int SqlCommandTimeout { get; set; }

/// <summary>
		/// The name of the SSIS catalog to deploy to.
		/// This property is optional. The default value is "SSISDB".
		/// </summary>
		public string Catalog { get; set; }

		public DeployProjectToCatalogTask()
		{
			Catalog = "SSISDB";
			CreateFolder = true;
SqlCommandTimeout = 300;
		}

		public override bool Execute()
		{
			bool result = true;
			var csb = new SqlConnectionStringBuilder
				{
					DataSource = Instance, IntegratedSecurity = true, InitialCatalog = Catalog, ConnectTimeout = 600
				};

			Log.LogMessage(SR.ConnectingToServer(csb.ConnectionString));

			using (var conn = new SqlConnection(csb.ConnectionString))
			{
				try
				{
					conn.Open();
				}
				catch (Exception e)
				{
					Log.LogError(SR.ConnectionError);
					Log.LogErrorFromException(e);
					return false;
				}

				foreach (var taskItem in DeploymentFile)
				{
					try
					{
						Log.LogMessage("------");

						string projectPath = taskItem.ItemSpec;

						if (CreateFolder)
						{
							EnsureFolderExists(conn, Folder);
						}

						string projectName = Path.GetFileNameWithoutExtension(projectPath);
						var bytes = File.ReadAllBytes(projectPath);

var deploymentCmd = GetDeploymentCommand(conn, Folder, projectName, bytes, SqlCommandTimeout);

						try
						{
							Log.LogMessage(SR.DeployingProject(projectPath));
							deploymentCmd.ExecuteNonQuery();
						}
						catch (Exception)
						{
							Log.LogError(SR.DeploymentFailed);
							throw;
						}

					}
					catch (Exception e)
					{
						Log.LogErrorFromException(e, true);
						result = false;
					}
				}
			}

			return result;
		}

		private void EnsureFolderExists(SqlConnection connection, string folder)
		{
if (!FolderExists(connection, folder, SqlCommandTimeout))
			{
				CreateCatalogFolder(connection, folder);
			}
		}

private static bool FolderExists(SqlConnection connection, string folder, int SqlCommandTimeout)
{
var cmd = GetFolderCommand(connection, folder, SqlCommandTimeout);
var folderId = cmd.ExecuteScalar();
return (folderId != null && folderId != DBNull.Value);
}

private void CreateCatalogFolder(SqlConnection connection, string folder)
		{
			var cmd = new SqlCommand("[catalog].[create_folder]", connection) {CommandType = CommandType.StoredProcedure};
cmd.CommandTimeout = SqlCommandTimeout;
			cmd.Parameters.AddWithValue("folder_name", folder);

			Log.LogMessage(SR.CreatingFolder(folder));
			cmd.ExecuteNonQuery();
		}

private static SqlCommand GetFolderCommand(SqlConnection connection, string folder, int SqlCommandTimeout)
		{
			var cmd = new SqlCommand("SELECT folder_id FROM [catalog].[folders] WHERE name = @FolderName", connection);
cmd.CommandTimeout = SqlCommandTimeout;
			cmd.Parameters.AddWithValue("@FolderName", folder);

			return cmd;
		}

private static SqlCommand GetDeploymentCommand(SqlConnection connection, string folder, string name, byte[] project, int SqlCommandTimeout)
		{
			// build the deployment command
			var cmd = new SqlCommand("[catalog].[deploy_project]", connection) { CommandType = CommandType.StoredProcedure };
cmd.CommandTimeout = SqlCommandTimeout;
			cmd.Parameters.AddWithValue("folder_name", folder);
			cmd.Parameters.AddWithValue("project_name", name);
			cmd.Parameters.AddWithValue("project_stream", project);
			cmd.Parameters.AddWithValue("operation_id", SqlDbType.BigInt).Direction = ParameterDirection.Output;

			return cmd;
		}
	}
}