SQLSATURDAY #130 Demo 4

/*******************************************************************
 *
 * 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
 * Add Data Flow
 *
 * Class 4- 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.Wrapper;
using Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask;


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

        public void CreatePackage()
        {
            String SqlCmd = "Select count (*) from information_schema.tables";
            String Description = "SQLSaturday Demo";
            //Add Variable to package
            Variable mvar = myPackage.Variables.Add("iDestCnt", false, "User", 0);
            //Add and get executable reference to a SQLTAsk Component
            Executable execSqlTask = AddSQLExecutable("STOCK:SQLTASk");
            //Wrap the executable in a TaskHost
            AddSQLTask(execSqlTask, "SQLTASK DEMO", "destination", SqlCmd, mvar.Name);
            //Add a Sequence Container
            Executable execSequence = AddSQLExecutable("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;

            //Add Work Flow
            Executable execDataFlow = seqSequence.Executables.Add("STOCK:PipelineTask");
            //Wrapper
            TaskHost thDataFlow = (TaskHost)execDataFlow;
            thDataFlow.Name = "DataFlow Demo";
            //reference thHost pipe
            MainPipe dfDataFlow = (MainPipe)thDataFlow.InnerObject;
           

            String SaveLocation = @"c:\\temp";
            SavePackage(SaveLocation);


        }
        public Package GetSetPackage
        {
            get { return myPackage; }
            set { myPackage = value; }

        }
        public String PackageName
        {
            set { myPackage.Name = value; }
        }

       
        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;

        }
        private void AddSQLTask(Executable exec, String TaskName,String Connection, String SqlCmd,String VariableName)
        {
             TaskHost sqlTaskHost = (TaskHost)exec;
            //Add Properties
            sqlTaskHost.Properties["Name"].SetValue(sqlTaskHost,TaskName);
            sqlTaskHost.Properties["Description"].SetValue(sqlTaskHost, TaskName);
            sqlTaskHost.Properties["Connection"].SetValue(sqlTaskHost,Connection);
            sqlTaskHost.Properties["SqlStatementSource"].SetValue(sqlTaskHost, SqlCmd);
            IDTSExecuteSQL iexecSqlTask = (IDTSExecuteSQL)sqlTaskHost.InnerObject;
            iexecSqlTask.ResultSetType = ResultSetType.ResultSetType_SingleRow;
            iexecSqlTask.SqlStatementSourceType = SqlStatementSourceType.DirectInput;
            IDTSResultBinding isqlTaskResult = iexecSqlTask.ResultSetBindings.Add();
            isqlTaskResult.DtsVariableName = VariableName;
        }
        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);

            }
        }


        private Executable AddSQLExecutable(String MONIKER)
        {
            Executable exec = myPackage.Executables.Add(MONIKER);
            return exec;
        }

        protected void Dispose(bool disposing)
        {
            if (disposing)
            {
                // dispose managed resources
                myPackage.Dispose();
            }
            // free native resources
        }

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