SQL Saturday Presentation Demo#2

Empty Package : Add Connections and Loggings

/*******************************************************************

 *

 * Jorge Novo

 * SQLSaturday SSIS Demostration

 * jorge.novo@gmail.com

 * http://etldevelopernotes.blogspot.com/

 * 4/27/2012

 *

 * Package to demostrate an empty ssis package

 * Add Connection Strings and loggings

 *

 * Class 2- 7

 *

 *

 *

 * *****************************************************************/

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.IO;

using Microsoft.SqlServer.Dts.Runtime;

namespace SQLSaturday130

{

    public class Demo2 : IDisposable

    {

        protected const String SaveLocation = @"c:\\temp";

        protected Package myPackage = new Package();

       

        public void CreatePackage()

        {

         

         

                SavePackage(SaveLocation);

          

        }

        public Package GetSetPackage

        {

            get { return myPackage; }

            set { myPackage = value; }

        }

        public String PackageName

        {

            set { myPackage.Name = value; }

        }

        private void SavePackage(String DirectoryPath)

        {

            String FullFilePath = DirectoryPath + "\\" + myPackage.Name.ToString() + ".dtsx";

            if (File.Exists(FullFilePath))

            {

                File.Delete(FullFilePath);

                Application app = new Application();

                app.SaveToXml(FullFilePath, myPackage, null);

            }

            else

            {

                Application app = new Application();

                app.SaveToXml(FullFilePath, myPackage, null);

            }

        }

        public void AddLogging(String ConnectionName, Boolean Enable)

        {

            LogProvider pkLogging;

            pkLogging = myPackage.LogProviders.Add("DTS.LogProviderSQLServer.2");

            pkLogging.ConfigString = ConnectionName;

            pkLogging.Description = "SQL Server Logging ";

            myPackage.LoggingOptions.SelectedLogProviders.Add(pkLogging);

            myPackage.LoggingOptions.EventFilterKind = Microsoft.SqlServer.Dts.Runtime.DTSEventFilterKind.Inclusion;

            myPackage.LoggingOptions.EventFilter = new String[] { "OnPreExecute", "OnPostExecute", "OnError" };

            switch (Enable)

            {

                case true:

                    myPackage.LoggingMode = Microsoft.SqlServer.Dts.Runtime.DTSLoggingMode.Enabled;

                    break;

                case false:

                    myPackage.LoggingMode = Microsoft.SqlServer.Dts.Runtime.DTSLoggingMode.Disabled;

                    break;

                default:

                    myPackage.LoggingMode = Microsoft.SqlServer.Dts.Runtime.DTSLoggingMode.Disabled;

                    break;

            }

        }

        public void AddOLEDBConnection(String ConnectionName, String ConnectionStr)

        {

            ConnectionManager ConMgr =myPackage.Connections.Add("OLEDB");

            ConMgr.ConnectionString = ConnectionStr + "Packet Size=32076;";

            ConMgr.Name = ConnectionName;

            ConMgr.Description = "SQL OLEDB using " + ConnectionName;

        }

        protected void Dispose(bool disposing)

        {

            if (disposing)

            {

                // dispose managed resources

                myPackage.Dispose();

            }

            // free native resources

        }

        public void Dispose()

        {

            Dispose(true);

            GC.SuppressFinalize(this);

        }

    }

}