Monday, September 23, 2013

Script with OLE DB connections


#region Namespaces
using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.Common;
using System.Data.OleDb;
using Wrap = Microsoft.SqlServer.Dts.Runtime.Wrapper;
// Add referenc in menu Project / Add Refference / Microsoft.SqlServer.DtsRuntimeWrap
#endregion

namespace ST_fb053e9cb2994d049ff4ca5a92cd1b7a
{
       [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
       
        public void Main()
        {
         
            string PackageName ="";
            string ExecutionGuid ="";
            string CounterName ="";
            string CounterValue ="";
        

            for (int i = 0; i <= (Dts.Variables.Count -1); i++)
            {
                if (Dts.Variables[i].Name.StartsWith("PackageName") )
                {
                     PackageName = Dts.Variables[i].Value.ToString();
                }

                 if (Dts.Variables[i].Name.StartsWith("ExecutionInstanceGUID") )
                {
                    ExecutionGuid = Dts.Variables[i].Value.ToString();
                }
            }


            for (int i = 0; i <= (Dts.Variables.Count - 1); i++)
            {
                if (Dts.Variables[i].Name.StartsWith("RC"))
                {
                   
                    CounterName = System.Text.RegularExpressions.Regex.Replace(Dts.Variables[i].Name.ToString().Substring(2), "[A-Z]", " $0");
                    CounterValue = Dts.Variables[i].Value.ToString();
                    //MessageBox.Show(CounterName);
                    string CommandText = "";
                    CommandText = "INSERT INTO MGT.[Audit].[ProcessCounts] ([ExecutionGuid],[PackageName],[CounterName],[Value])";
                    CommandText = CommandText + "VALUES('" + ExecutionGuid + "','" + PackageName + "','" + CounterName + "'," + CounterValue + ")";
                    ConnectionManager cm = Dts.Connections["Cn_MGT"];
                    Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100 cmParams = cm.InnerObject as Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100;

                    using (OleDbConnection conn = cmParams.GetConnectionForSchema() as OleDbConnection)
                    {
                        OleDbCommand command = new OleDbCommand(CommandText, conn);
                        command.ExecuteNonQuery();
                    }

                  
                }

               
            }                     
            Dts.TaskResult = (int)ScriptResults.Success;
        }

        #region ScriptResults declaration
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

    }
}

Till Next Time

No comments: