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);
        }
    }
}

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);

        }

    }

}

SQLSaturday #130 Presentation

/*******************************************************************
 *
 * Jorge Novo
 * SQLSaturday SSIS Demostration
 * jorge.novo@gmail.com
 * http://etldevelopernotes.blogspot.com/
 * 4/27/2012
 *
 * Package to demostrate an empty ssis package
 *
 * Class 1- 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 Demo1 : IDisposable
    {
        protected Package myPackage = new Package();
        const String SaveLocation = @"c:\\temp";

        public void CreatePackage()
        {
        
            using (myPackage = new Package())
            {
                myPackage.Name = "Demo1_EmptyPackage";
              
               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);
              
            }
        }
        protected void Dispose(bool disposing)
        {
            if (disposing)
            {
                // dispose managed resources
                myPackage.Dispose();
            }
            // free native resources
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
    }
}

SQL Saturday preparation

Today I started to get some ideas about my presentation in SQL Saturday.
First I will be creating a simple package :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
namespace LibSQLSaturday
{
    /* Demostration of an Empty SSIS Package */
    public class Demo1
    {
        private Package myPackage;
        private  String FullFilePath;
       

        public Demo1()
        {
           myPackage = new Package();
        }
        public void SavePackage()
        {
            Application app = new Application();
            app.SaveToXml(FullFilePath+"\\"+myPackage.Name.ToString ()+".dtsx",myPackage, null);
       }
    public string Name
    {
        get { return myPackage.Name.ToString (); }
        set { myPackage.Name = value; }
    }
        public String SetFilePath
        {
            set {FullFilePath = value;}
            get { return FullFilePath; }
        }
   }
}
And then continue to add up to it ...So, kind of like Demo1,2,3,etc as a progression and adding components to it.