Creating Issue via SOAP API does not retain created date

[Jeffery Fernandez]

I am in the process of migrating data from a legacy support system. While importing the issues, I am setting the created date field to the actual date the issue was created in the legacy support system. However, JIRA ignores the set data and marks the ticket as created the date/time the import is run.

Is there a work around this, or am I doing something wrong?

[Jeffery Fernandez · Accepted solution]

What I ended up doing was to use the CSV import functionality. I had to write a script which generated the import file. Thanks everyone for your suggestions.

[Bob Swift (personal)]

You are not doing anything wrong. JIRA ignores it and puts in current time :(.

[pratikraj11]

why JIRA ignores it? Is there any workaround to solve this problem?

[Luis Mayora]

I created this C# class to manage some JIRA limitations and one of them is the JIRA Date Created. Hope it is useful for you. I am not a C# expert but this is what i got so far.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace nreca.jira.migration
{
    public class DAOSqlJira
    {
        private SqlTransaction oSqlTransaction;
        private SqlCommand oSqlCommand;
        private string oJiraDateQuery;
        private string oJiraDateUpdated;
        private string oGetUserQuery;
        private string oResetCounter;
        private string oCommentDateUpdate;
        private string oAttachmentDate;
        private string oComponent;
        private string oFixVersion;

        public DAOSqlJira()
        {
            oJiraDateQuery = "update jiraschema.jiraissue set created = '%created%' where pkey = '%ikey%' ";
            oJiraDateUpdated = "update jiraschema.jiraissue set updated = '%updated%' where pkey = '%ikey%' ";
            oGetUserQuery = "select [uid],firstname,lastname,emailaddress from StaffMembers where emailaddress like '$email$'";
            oResetCounter = "update jiraschema.project set pcounter=0 where pkey='%pkey%'";
            oCommentDateUpdate = "update jiraschema.jiraaction set created='%datecreated%',author='%author%' where ID=%commentid% ";
            oAttachmentDate = "update jiraschema.fileattachment set created='%datecreated%',author='%author%' where ID=%attachmentid% ";
            oComponent = "select id from jiraschema.component where project='%project%' and cname='%cname%' ";
            oFixVersion = "select id from jiraschema.projectversion where project='%project%' and vname='%vname%' ";

        }

        public SqlConnection CreateConnection(string connectionString)
        {
            return new SqlConnection(connectionString);

        }


        public void UpdateAttachmentCreatedDate(string attachmentId, string dateCreated, string updateAuthor)
        {

            SqlConnection oJiraConnection = null;
            try
            {
                oJiraConnection = CreateConnection(ConfigurationManager.ConnectionStrings["jiradb"].ConnectionString);
                DateTime oDate = Convert.ToDateTime(dateCreated);
                string qry = oAttachmentDate.Replace("%datecreated%", oDate.ToString("yyyy-MM-dd h:mm:ss tt")).Replace("%author%", updateAuthor).Replace("%attachmentid%", attachmentId);

                oJiraConnection.Open();
                oSqlTransaction = oJiraConnection.BeginTransaction(IsolationLevel.ReadCommitted);

                oSqlCommand = new SqlCommand(qry, oJiraConnection, oSqlTransaction);
                int numRows = oSqlCommand.ExecuteNonQuery();

                if (numRows >= 1)

                    oSqlTransaction.Commit();
                else
                    oSqlTransaction.Rollback();

            }
            catch (SqlException e)
            {
                oSqlTransaction.Rollback();
                throw e;
            }
            finally
            {
                oJiraConnection.Close();
            }

        }


        public void ResetIssueCounter(string projectKey)
        {
            SqlConnection oJiraConnection = null;
            try
            {
                oJiraConnection = CreateConnection(ConfigurationManager.ConnectionStrings["jiradb"].ConnectionString);

                string qry = oResetCounter.Replace("%pkey%", projectKey);

                oJiraConnection.Open();
                oSqlTransaction = oJiraConnection.BeginTransaction(IsolationLevel.ReadUncommitted);

                oSqlCommand = new SqlCommand(qry, oJiraConnection, oSqlTransaction);
                int numRows = oSqlCommand.ExecuteNonQuery();

                if (numRows != 1)
                {
                    oSqlTransaction.Rollback();
                    throw new Exception("No rows were updated");
                }
                oSqlTransaction.Commit();


            }
            catch (SqlException e)
            {
                oSqlTransaction.Rollback();
                throw e;
            }
            finally
            {
                oJiraConnection.Close();
            }
        }


        public void CreateJIRALink(string issueSourceId, string issueDestinationId, string linkType, string linkSequence)
        {

            SqlConnection oJiraConnection = null;
            try
            {
                oJiraConnection = CreateConnection(ConfigurationManager.ConnectionStrings["jiradb"].ConnectionString);
                oJiraConnection.Open();
                oSqlTransaction = oJiraConnection.BeginTransaction(IsolationLevel.ReadCommitted);

                string qryLastLinkId = " select max(id) FROM jiraschema.issuelink ";

                SqlCommand oCommandLastLink = new SqlCommand(qryLastLinkId, oJiraConnection, oSqlTransaction);

                int id =  int.Parse(oCommandLastLink.ExecuteScalar().ToString()) + 1;

                string qry = "insert into jiraschema.issuelink(ID,LINKTYPE,SOURCE,DESTINATION) values (" + id.ToString() + "," + linkType + "," + issueSourceId + "," + issueDestinationId + ")";

                oSqlCommand = new SqlCommand(qry, oJiraConnection, oSqlTransaction);
                int numRows = oSqlCommand.ExecuteNonQuery();

                if (numRows != 1)
                {
                    oSqlTransaction.Rollback();
                    throw new Exception("No rows were updated");
                }
                oSqlTransaction.Commit();


            }
            catch (SqlException e)
            {
                oSqlTransaction.Rollback();
                throw e;
            }
            finally
            {
                oJiraConnection.Close();
            }


        }

        /// <summary>
        /// Updates the JIRA Issue table with the Date Created Value from TSR Access DB
        /// This is due the JIRA API limitation to set the Date Created when creating an issue.
        /// </summary>
        /// <param name="issueKey"></param>
        /// <param name="dateCreated"></param>
        public void UpdateDateCreated(string issueKey, string dateCreated)
        {
            SqlConnection oJiraConnection = null;

            string qry = "";
            try
            {
                oJiraConnection = CreateConnection(ConfigurationManager.ConnectionStrings["jiradb"].ConnectionString);
                DateTime oDateCreated = Convert.ToDateTime(dateCreated);

                oJiraDateQuery = "update jiraschema.jiraissue set created = '%created%' where pkey = '%ikey%' ";
                qry = oJiraDateQuery.Replace("%created%", oDateCreated.ToString("yyyy-MM-dd h:mm:ss tt")).Replace("%ikey%", issueKey);


                oJiraConnection.Open();
                oSqlTransaction = oJiraConnection.BeginTransaction(IsolationLevel.ReadCommitted);

                oSqlCommand = new SqlCommand(qry, oJiraConnection, oSqlTransaction);
                int numRows = oSqlCommand.ExecuteNonQuery();

                if (numRows != 1)
                {
                    oSqlTransaction.Rollback();
                    throw new Exception("No rows were updated");
                }
                oSqlTransaction.Commit();


            }
            catch (SqlException e)
            {
                oSqlTransaction.Rollback();
                throw e;
            }
            finally
            {
                oJiraConnection.Close();
            }

        }


        /// <summary>
        /// Updates the JIRA Issue table with the Date Created Value from TSR Access DB
        /// This is due the JIRA API limitation to set the Date Created when creating an issue.
        /// </summary>
        /// <param name="issueKey"></param>
        /// <param name="dateCreated"></param>
        public void UpdateResolutionDate(string issueKey, string dateClosed)
        {
            SqlConnection oJiraConnection = null;
            string qry = "";

            try
            {
                oJiraConnection = CreateConnection(ConfigurationManager.ConnectionStrings["jiradb"].ConnectionString);
                DateTime oDateCreated = Convert.ToDateTime(dateClosed);

                oJiraDateQuery = "update jiraschema.jiraissue set resolutiondate = '%closed%' where pkey = '%ikey%' ";
                qry = oJiraDateQuery.Replace("%closed%", oDateCreated.ToString("yyyy-MM-dd h:mm:ss tt")).Replace("%ikey%", issueKey);

                oJiraConnection.Open();
                oSqlTransaction = oJiraConnection.BeginTransaction(IsolationLevel.ReadCommitted);

                oSqlCommand = new SqlCommand(qry, oJiraConnection, oSqlTransaction);
                int numRows = oSqlCommand.ExecuteNonQuery();

                if (numRows != 1)
                {
                    oSqlTransaction.Rollback();
                    throw new Exception("No rows were updated");
                }
                oSqlTransaction.Commit();


            }
            catch (SqlException e)
            {
                oSqlTransaction.Rollback();
                throw e;
            }
            finally
            {
                oJiraConnection.Close();
            }

        }


        public void UpdateCommentCreatedDate(string commentId, string dateCreated, string updateAuthor)
        {

            SqlConnection oJiraConnection = null;
            try
            {
                oJiraConnection = CreateConnection(ConfigurationManager.ConnectionStrings["jiradb"].ConnectionString);
                DateTime oDate = Convert.ToDateTime(dateCreated);
                string qry = oCommentDateUpdate.Replace("%datecreated%", oDate.ToString("yyyy-MM-dd h:mm:ss tt")).Replace("%author%", updateAuthor).Replace("%commentid%", commentId);

                oJiraConnection.Open();
                oSqlTransaction = oJiraConnection.BeginTransaction(IsolationLevel.ReadCommitted);

                oSqlCommand = new SqlCommand(qry, oJiraConnection, oSqlTransaction);
                int numRows = oSqlCommand.ExecuteNonQuery();

                if (numRows >= 1)

                    oSqlTransaction.Commit();
                else
                    oSqlTransaction.Rollback();

            }
            catch (SqlException e)
            {
                oSqlTransaction.Rollback();
                throw e;
            }
            finally
            {
                oJiraConnection.Close();
            }

        }

        /// <summary>
        /// Updates the JIRA Issue table with the Date Created Value from TSR Access DB
        /// This is due the JIRA API limitation to set the Date Created when creating an issue.
        /// </summary>
        /// <param name="issueKey"></param>
        /// <param name="dateCreated"></param>
        public void UpdateDateUpdated(string issueKey, string dateUpdated)
        {
            SqlConnection oJiraConnection = null;
            try
            {
                oJiraConnection = CreateConnection(ConfigurationManager.ConnectionStrings["jiradb"].ConnectionString);
                DateTime oDate = Convert.ToDateTime(dateUpdated);

                string qry = oJiraDateUpdated.Replace("%updated%", oDate.ToString("yyyy-MM-dd h:mm:ss tt")).Replace("%ikey%", issueKey);

                oJiraConnection.Open();
                oSqlTransaction = oJiraConnection.BeginTransaction(IsolationLevel.ReadCommitted);

                oSqlCommand = new SqlCommand(qry, oJiraConnection, oSqlTransaction);
                int numRows = oSqlCommand.ExecuteNonQuery();

                if (numRows != 1)
                {
                    oSqlTransaction.Rollback();
                    throw new Exception("No rows were updated");
                }
                oSqlTransaction.Commit();


            }
            catch (SqlException e)
            {
                oSqlTransaction.Rollback();
                throw e;
            }
            finally
            {
                oJiraConnection.Close();
            }

        }

        public string GetComponentId(string componentName,string project)
        {
            string result = "";
            SqlConnection oJiraConnection = null;
            try
            {
                oJiraConnection = CreateConnection(ConfigurationManager.ConnectionStrings["jiradb"].ConnectionString);

                string qry = oComponent.Replace("%cname%", componentName).Replace("%project%",project);

                oJiraConnection.Open();

                oSqlCommand = new SqlCommand(qry, oJiraConnection);
                SqlDataReader oReader = oSqlCommand.ExecuteReader(CommandBehavior.SingleRow);

                if (oReader.HasRows)
                {
                    while (oReader.Read() == true)
                    {
                        result = oReader["ID"].ToString();
                    }
                }
                else 
                {
                   result = GetComponentId("no Component",project);
                }

                oReader.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                oJiraConnection.Close();
            }

            return result;
        }


        public string GetFixVersionId(string componentName, string project)
        {
            string result = "";
            SqlConnection oJiraConnection = null;
            try
            {
                oJiraConnection = CreateConnection(ConfigurationManager.ConnectionStrings["jiradb"].ConnectionString);

                string qry = oFixVersion.Replace("%vname%", componentName).Replace("%project%", project);

                oJiraConnection.Open();

                oSqlCommand = new SqlCommand(qry, oJiraConnection);
                SqlDataReader oReader = oSqlCommand.ExecuteReader(CommandBehavior.SingleRow);

                if (oReader.HasRows)
                {
                    while (oReader.Read() == true)
                    {
                        result = oReader["ID"].ToString();
                    }
                }
                oReader.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                oJiraConnection.Close();
            }

            return result;
        }



        public List<UserMigration> GetUsers(string[] lastnames)
        {
            SqlConnection oIdentityConnection = null;
            List<UserMigration> listUsers = new List<UserMigration>();
            oIdentityConnection = CreateConnection(ConfigurationManager.ConnectionStrings["imanagement"].ConnectionString);

            try
            {
                oIdentityConnection.Open();
                //DataSet dtTSRs = new DataSet();
                //dtTSRs.Tables.Add("TSR");

                foreach (string lastname in lastnames)
                {
                    string query = oGetUserQuery.Replace("%lastname%", lastname);

                    oSqlCommand = new SqlCommand(query, oIdentityConnection);
                    SqlDataReader oReader = oSqlCommand.ExecuteReader(CommandBehavior.SingleRow);

                    while (oReader.Read() == true)
                    {
                        UserMigration oUser = new UserMigration();

                        oUser.Uid = oReader["uid"].ToString();
                        oUser.FirstName = oReader["firstname"].ToString();
                        oUser.LastName = oReader["lastname"].ToString();
                        oUser.EmailAddress = oReader["emailaddress"].ToString();

                        listUsers.Add(oUser);
                    }
                }

            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                oIdentityConnection.Close();
            }

            return listUsers;
        }

       

        public UserMigration GetUser(string email)
        {
            SqlConnection oIdentityConnection = null;
            UserMigration userTsr = null;
            oIdentityConnection = CreateConnection(ConfigurationManager.ConnectionStrings["imanagement"].ConnectionString);

            try
            {
                oIdentityConnection.Open();
                string query = oGetUserQuery.Replace("$email$", "%" + email);
                oSqlCommand = new SqlCommand(query, oIdentityConnection);
                SqlDataReader oReader = oSqlCommand.ExecuteReader(CommandBehavior.SingleRow);

                while (oReader.Read() == true)
                {
                    userTsr = new UserMigration();
                    userTsr.Uid = oReader["uid"].ToString();
                    userTsr.FirstName = oReader["firstname"].ToString();
                    userTsr.LastName = oReader["lastname"].ToString();
                    userTsr.EmailAddress = oReader["emailaddress"].ToString();
                }
                oReader.Close();

            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                oIdentityConnection.Close();
            }

            return userTsr;
        }


    }
}