February 07, 2014

Saving Ssis Package int Xml Column using C#

Finally I was able to recoup some old code and making it work using C#
Sometimes you don't want to give access to your developers to a production environment other than read only, however, when tracing ssis packages that fail what better way than to store it into a table that now developers can read and re-create the packages.
  // save XML package into errorlog table
        private void StoreSsisPackage(ConnectionManager host)
        {
            int LogId = (int)vars["User::intETLLogId"].Value;
            StringBuilder strbSQLCmd = new StringBuilder();
            XmlDocument xlmdoc = new XmlDocument();
            String ServerName = (String)host.Properties["ServerName"].GetValue(host);
            String DatabaseName = (string)host.Properties["InitialCatalog"].GetValue(host);
            String StoreProc = "Log.SsisStorage_InsUpd";

            ssis.SaveToXML(ref xlmdoc, null, null);
            strbSQLCmd.Append("Server=");
            strbSQLCmd.Append(ServerName);
            strbSQLCmd.Append(";Database=");
            strbSQLCmd.Append(DatabaseName);
            strbSQLCmd.Append(";Trusted_Connection=TRUE");

            SqlConnection sqlCon = new SqlConnection(strbSQLCmd.ToString());
            using (DbCommand Cmd = sqlCon.CreateCommand())
            {
                Cmd.CommandType =  System.Data.CommandType.StoredProcedure;
                Cmd.CommandText = StoreProc;
                Cmd.Parameters.Add(new SqlParameter("@ETLLogId", System.Data.SqlDbType.Int)
                {
                    Value = LogId
                });
                Cmd.Parameters.Add(new SqlParameter("@pkg", System.Data.SqlDbType.Xml)
                {
                   Value = new System.Data.SqlTypes.SqlXml(new XmlTextReader(xlmdoc.InnerXml,XmlNodeType.Document,null))
                });
                sqlCon.Open();
                DbTransaction tran = sqlCon.BeginTransaction();
                Cmd.Transaction = tran;
                try
                {
                    String strCmd = Cmd.CommandText.ToString();
                    Cmd.ExecuteNonQuery();

                    tran.Commit();
                    sqlCon.Close();
                }
                catch (Exception e)
                {
                   
                   MessageBox.Show(e.ToString());
                    tran.Rollback();
                    sqlCon.Close();
                    throw;
                }
            }

No comments:

Post a Comment

Contact Form

Name

Email *

Message *