SQL Saturday #130 DEMO3

/*******************************************************************
 *
 * 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
 * Add SQL Task and Sequence Container
 *
 *
 * Class 3- 7
 *
 *
 *
 * *****************************************************************/


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask;


/*
 * Adding Sequence Container
 * with SQLTask Work Flow Source and Destination
 */
namespace SQLSaturday130
{
    public class Demo3 : IDisposable
    {
        protected Package myPackage = new Package();

        public void CreatePackage()
        {
            String SqlCmd = "Select * from information_schema.tables";
            String Description = "SQLSaturdayDemo";
            //Add and get executable reference to a SQLTAsk Component
            Executable execSqlTask = myPackage.Executables.Add("STOCK:SQLTASk");
            //Wrap the executable in a TaskHost
            TaskHost sqlTaskHost = (TaskHost)execSqlTask;
            //Add Properties
            sqlTaskHost.Properties["Name"].SetValue(sqlTaskHost,"SQLTASK DEMO");
            sqlTaskHost.Properties["Description"].SetValue(sqlTaskHost,Description);
            sqlTaskHost.Properties["Connection"].SetValue(sqlTaskHost, "destination");
            sqlTaskHost.Properties["SqlStatementSource"].SetValue(sqlTaskHost, SqlCmd);
            //Add a Sequence Container
            Executable execSequence = myPackage.Executables.Add("STOCK:Sequence");
            Sequence seqSequence = (Sequence)execSequence;
            //add Properties
            seqSequence.Name = "SEQUENCE Demo";
            seqSequence.LoggingMode = DTSLoggingMode.UseParentSetting;
            seqSequence.Description = Description;

            //Set precedence Constraint
            PrecedenceConstraint SqlTask2Sequece = myPackage.PrecedenceConstraints.Add(execSqlTask, execSequence);
            SqlTask2Sequece.Value = DTSExecResult.Success;
           
            String SaveLocation = @"c:\\temp";
            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);
        }
    }
}