SQLSaturday #130 Demo 5

/*******************************************************************
 *
 * 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
 * Add Source
 * Add Destination
 *
 * Class 5- 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.Runtime.Wrapper;
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 Demo5 : IDisposable
    {
        private Microsoft.SqlServer.Dts.Runtime.Package myPackage = new Microsoft.SqlServer.Dts.Runtime.Package();

        public void CreatePackage()
        {
            String SqlCmd = "Select count (*) from information_schema.tables";
            String Description = "SQLSaturday Demo";
            String SourceSchTable = "tblDemo1";
            String DestinationSchTable = "tblDemo2";
            //Add Variable to package
            Microsoft.SqlServer.Dts.Runtime.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");
            Microsoft.SqlServer.Dts.Runtime.Sequence seqSequence = (Microsoft.SqlServer.Dts.Runtime.Sequence)execSequence;
            //add Properties
            seqSequence.Name = "SEQUENCE Demo";
            seqSequence.LoggingMode = Microsoft.SqlServer.Dts.Runtime.DTSLoggingMode.UseParentSetting;
            seqSequence.Description = Description;

            //Set precedence Constraint
            Microsoft.SqlServer.Dts.Runtime.PrecedenceConstraint SqlTask2Sequece = myPackage.PrecedenceConstraints.Add(execSqlTask, execSequence);
            SqlTask2Sequece.Value = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success;
            //Add Empty Work Flow

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

            //Add Source Component to package
            IDTSComponentMetaData100 icmdSource = AddComponentMetadata(dfDataFlow, "SourceComponet");
            //Add Destination
            IDTSComponentMetaData100 icmdDestination = AddComponentMetadata(dfDataFlow, "DestinationComponent");
            icmdSource.ComponentClassID = "DTSAdapter.OleDbSource.2";
            icmdDestination.ComponentClassID = "DTSAdapter.OLEDBDestination.2";

            //Source Configuration
            ConnectionManager source = myPackage.Connections["source"];
            ConnectionManager destination = myPackage.Connections["destination"];
            CManagedComponentWrapper mcwSource = icmdSource.Instantiate();
            mcwSource.ProvideComponentProperties();
            icmdSource.RuntimeConnectionCollection[0].ConnectionManagerID = source.ID;
            icmdSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(source);

            //mcwSource.SetComponentProperty("AccessMode",2)//SQLCmd
            //mcwSource.SetComponentProperty("SqlCommand",SqlCmd);
            mcwSource.SetComponentProperty("AccessMode", 0); //TableOrView
            mcwSource.SetComponentProperty("OpenRowset", SourceSchTable);
            RefreshMetadata(mcwSource);

            //Destination Configuration
          
            icmdDestination.ValidateExternalMetadata = true;
         
            CManagedComponentWrapper mcwDestination = icmdDestination.Instantiate();
          
            mcwDestination.ProvideComponentProperties();
            mcwDestination.SetComponentProperty("FastLoadKeepIdentity", true);
            icmdDestination.Name = "tblDemo2";
            icmdDestination.Description = "tblDemo2";
            icmdDestination.RuntimeConnectionCollection[0].ConnectionManagerID = destination.ID;
            icmdDestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(destination);
            mcwDestination.SetComponentProperty("AccessMode", 0);//FastLoad
            mcwDestination.SetComponentProperty("OpenRowset", DestinationSchTable);
            mcwDestination.Validate();
          
            RefreshMetadata(mcwDestination);
    

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


        }
        public Microsoft.SqlServer.Dts.Runtime.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 IDTSComponentMetaData100 AddComponentMetadata(MainPipe wf,String ComponentName)
        {
            IDTSComponentMetaData100 icmd = wf.ComponentMetaDataCollection.New();
            return icmd;
        }
       
        private void AddSQLTask(Executable exec, String TaskName, String Connection, String SqlCmd, String VariableName)
        {
            Microsoft.SqlServer.Dts.Runtime.TaskHost sqlTaskHost = (Microsoft.SqlServer.Dts.Runtime.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);
                Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
                app.SaveToXml(FullFilePath, myPackage, null);

            }
            else
            {
                Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
                app.SaveToXml(FullFilePath, myPackage, null);

            }
        }

        private void RefreshMetadata(CManagedComponentWrapper mcw)
        {
             //Reinitialize the metadata, Refresh Columns
            mcw.AcquireConnections(null);
            mcw.ReinitializeMetaData();
            mcw.ReleaseConnections();

        }


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