SSIS 2012 Deployment Fail Revisited

( 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()

Background

As mentioned in my first post, we don’t use the SSIS Deployment Wizard in our automated builds as it does not work in Silent Mode when you have assembly references in the SSIS packages: it destroys those references and the dtsx packages fail whenever they run. To circumnavigate this, we use a custom build task written in C#. The source of this version can be found on CodePlex. This project contains Integration Services samples created by the SSIS product team. What we have done is take the DeployProjectToCatalogTask in the code samples (see screenshot below) and call it via MSBuild as you would any Custom Task. For info on how to write a Custom Task, this article is a good place to start.

2013-11-04 15_33_56-Microsoft SQL Server Community Samples_ Integration Services - Source Code - ComThis issue was only occurring in our automated builds, not when we deployed through the UI. So it was clear that our custom task was the issue. Although it was a timeout issue, there was no issue in connecting to the instance as running profiler confirmed that it was establishing a connection and running the commands. So it definitely was not a connection issue.

sproc1
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;
		}
	}
}

Summary

If you’re deploying several projects to the catalog frequently, and you have good Source Control, it’s still worth limiting the days of logs retained and the previous versions, as SSISDB can grow very large, and the maintenance task take a long time to clear the backlog of unwanted logs/projects. When deploying through automated builds, use the Silent Mode of the Integration Services Deployment Wizard unless you are in the same situation as us and have references to custom assemblies (for logging and file validation). And remember: There’s no problem that can;t be solved through Googling/Binging/Yahooing!

One thought on “SSIS 2012 Deployment Fail Revisited

  1. Pingback: MSBuildSsis2012 Now Available (…or Rather SSIS 2012 Deployment Fail Revisited…Again) | phoebix

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s