August 08, 2014

More fun loading file(s)


#region Help:  Introduction to the script task

/* Jorge Novo 
 * a .Net application within the context of an Integration Services control flow. 
 * 
 * Expand the other regions which have "Help" prefixes for examples of specific ways to use
 * Integration Services features within this script task. */
#endregion


#region Namespaces
using System;
using System.Data.SqlClient;
using System.Data.Common;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using TxBDD;
using SQLTask = Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask;
using Runtime = Microsoft.SqlServer.Dts.Runtime;
using Pipeline = Microsoft.SqlServer.Dts.Pipeline;
using RuntimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;
using PipelineWrapper = Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using System.Text;
using System.IO;
using System.Xml;
#endregion

namespace ST_a521b7186dae425b959c1d4e8c6625d2
{
    /// <summary>
    /// ScriptMain is the entry point class of the script.  Do not change the name, attributes,
    /// or parent of this class.
    /// </summary>
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
        #region Help:  Using Integration Services variables and parameters in a script
        /* To use a variable in this script, first ensure that the variable has been added to 
         * either the list contained in the ReadOnlyVariables property or the list contained in 
         * the ReadWriteVariables property of this script task, according to whether or not your
         * code needs to write to the variable.  To add the variable, save this script, close this instance of
         * Visual Studio, and update the ReadOnlyVariables and 
         * ReadWriteVariables properties in the Script Transformation Editor window.
         * To use a parameter in this script, follow the same steps. Parameters are always read-only.
         * 
         * Example of reading from a variable:
         *  DateTime startTime = (DateTime) Dts.Variables["System::StartTime"].Value;
         * 
         * Example of writing to a variable:
         *  Dts.Variables["User::myStringVariable"].Value = "new value";
         * 
         * Example of reading from a package parameter:
         *  int batchId = (int) Dts.Variables["$Package::batchId"].Value;
         *  
         * Example of reading from a project parameter:
         *  int batchId = (int) Dts.Variables["$Project::batchId"].Value;
         * 
         * Example of reading from a sensitive project parameter:
         *  int batchId = (int) Dts.Variables["$Project::batchId"].GetSensitiveValue();
         * */

        #endregion

        #region Help:  Firing Integration Services events from a script
        /* This script task can fire events for logging purposes.
         * 
         * Example of firing an error event:
         *  Dts.Events.FireError(18, "Process Values", "Bad value", "", 0);
         * 
         * Example of firing an information event:
         *  Dts.Events.FireInformation(3, "Process Values", "Processing has started", "", 0, ref fireAgain)
         * 
         * Example of firing a warning event:
         *  Dts.Events.FireWarning(14, "Process Values", "No values received for input", "", 0);
         * */
        #endregion

        #region Help:  Using Integration Services connection managers in a script
        /* Some types of connection managers can be used in this script task.  See the topic 
         * "Working with Connection Managers Programatically" for details.
         * 
         * Example of using an ADO.Net connection manager:
         *  object rawConnection = Dts.Connections["Sales DB"].AcquireConnection(Dts.Transaction);
         *  SqlConnection myADONETConnection = (SqlConnection)rawConnection;
         *  //Use the connection in some code here, then release the connection
         *  Dts.Connections["Sales DB"].ReleaseConnection(rawConnection);
         *
         * Example of using a File connection manager
         *  object rawConnection = Dts.Connections["Prices.zip"].AcquireConnection(Dts.Transaction);
         *  string filePath = (string)rawConnection;
         *  //Use the connection in some code here, then release the connection
         *  Dts.Connections["Prices.zip"].ReleaseConnection(rawConnection);
         * */
        #endregion


/// <summary>
        /// This method is called when this script task executes in the control flow.
        /// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
        /// To open Help, press F1.
        /// </summary>
        /// 
        // Global Variables
        Package ssis = new Package();
        Runtime.Application app = new Runtime.Application();
        Variables vars = null;

        public void Main()
        {
            try
            {

                


                //External Parent Variables
                Dts.VariableDispenser.LockForRead("User::strArchiveDirectory");
                Dts.VariableDispenser.LockForRead("User::strImportDirectory");
                Dts.VariableDispenser.LockForRead("User::strLoadDirectory");
                Dts.VariableDispenser.LockForRead("User::strSsisName");
                Dts.VariableDispenser.LockForRead("User::strBatchId");
                Dts.VariableDispenser.LockForRead("User::strDestConnStr");
                Dts.VariableDispenser.LockForRead("User::strDestSchema");
                Dts.VariableDispenser.LockForRead("User::strDestTableName");
                Dts.VariableDispenser.LockForRead("User::strDestServerName");
                Dts.VariableDispenser.LockForRead("User::strFileCodePage");
                Dts.VariableDispenser.LockForRead("User::strFileName");
                Dts.VariableDispenser.LockForRead("User::strFilePrefix");
                Dts.VariableDispenser.LockForRead("User::strFileSubfix");
                Dts.VariableDispenser.LockForRead("User::strSymbol");
                Dts.VariableDispenser.LockForRead("User::strFileExtension");
                Dts.VariableDispenser.LockForWrite("User::bolIsLoaded");
                Dts.VariableDispenser.LockForRead("User::intLFNFileId");
                Dts.VariableDispenser.LockForWrite("User::intRecordCount");
                Dts.VariableDispenser.LockForWrite("User::strErrorMessage");
                Dts.VariableDispenser.LockForRead("User::strStoreXmlCmd");
                Dts.VariableDispenser.LockForRead("User::intETLLogId");
                Dts.VariableDispenser.GetVariables(ref vars);

                //Local Variables
                String Archive = vars["User::strArchiveDirectory"].Value.ToString();
                String Load = vars["User::strLoadDirectory"].Value.ToString();
                String Import = vars["User::strImportDirectory"].Value.ToString();
                String SsisName = vars["User::strSsisName"].Value.ToString();
                String BatchId = vars["User::strBatchId"].Value.ToString();
                String DestConnectionStr = vars["User::strDestConnStr"].Value.ToString();
                String TableSchema = vars["User::strDestSchema"].Value.ToString();
                String TableName = vars["User::strDestTableName"].Value.ToString();
                String DestServer = vars["User::strDestServerName"].Value.ToString();
                String CodePage = vars["User::strFileCodePage"].Value.ToString();
                String FileName = vars["User::strFileName"].Value.ToString();
                String Prefix = vars["User::strFilePrefix"].Value.ToString().Trim();
                String SubFix = vars["User::strFileSubfix"].Value.ToString().Trim();
                String FileDelimite = vars["User::strSymbol"].Value.ToString().Trim();
                String FileExtension = "." + vars["User::strFileExtension"].Value.ToString().Trim();
                String TruncateCmd = "Truncate Table " + TableSchema + "." + TableName;
                String FillTableName = TableSchema + "." + TableName;

                int LFNFileId =(int) vars["User::intLFNFileId"].Value;

                //Compose Variables
                StringBuilder FilePattern = new StringBuilder();


                FilePattern.Append(Prefix);
                FilePattern.Append(FileName);
                FilePattern.Append(SubFix);
                FilePattern.Append(FileExtension);
                String xFullFilePath = GetFileName(Load, FilePattern.ToString());


                //Assign relevant package name and description
                ssis.Name = SsisName;
                ssis.Description = "Load Process of initial File";

                //Package Variables
                Runtime.Variable intRowCount = ssis.Variables.Add("intRowCount", false, "User", 0);
                Runtime.Variable strBatchId = ssis.Variables.Add("strBatchId", false, "User", BatchId);
                Runtime.Variable intLFNFileId = ssis.Variables.Add("intLfnFileId", false, "User", LFNFileId);
               

                //Add database Connections
                ConnectionManager destination = AddOLEDBConnection("destination", DestConnectionStr);
                ConnectionManager msdb = AddOLEDBConnection("msdb", Dts.Connections["msdb"].ConnectionString.ToString());
                ConnectionManager host = AddOLEDBConnection("host", Dts.Connections["host"].ConnectionString.ToString());

                AddLogging(msdb.Name, true);


                //Add Sequence Container 
                Executable execSequence = AddSQLExecutable("STOCK:Sequence");
                Runtime.Sequence rtSequence = (Runtime.Sequence)execSequence;

                //Sequence Properties
                rtSequence.Name = "File2FillTableImport";
                rtSequence.LoggingMode = Runtime.DTSLoggingMode.UseParentSetting;
                rtSequence.Description = "EIM Import Prototype";

                // Add SQLTask Component 
                Executable execFillRawSqlTask = rtSequence.Executables.Add("STOCK:SQLTASk");
                //Wrap the executable in a TaskHost 
                AddSQLTask(execFillRawSqlTask, "Truncate Fill Table", destination.Name.ToString(), TruncateCmd);



                //Add DataFlow
                Executable execRawDataFlow = rtSequence.Executables.Add("STOCK:PipelineTask");
                Microsoft.SqlServer.Dts.Runtime.TaskHost thRawDataFlow = (Microsoft.SqlServer.Dts.Runtime.TaskHost)execRawDataFlow;
                thRawDataFlow.Name = "RawFile2FillTable";
                PipelineWrapper.MainPipe mDataFlow = (PipelineWrapper.MainPipe)thRawDataFlow.InnerObject;

                // Add Precedence Constraint between SQLTask and workflow
                Runtime.PrecedenceConstraint SqlTask2WorkFlow = rtSequence.PrecedenceConstraints.Add(execFillRawSqlTask, execRawDataFlow);
                SqlTask2WorkFlow.Value = Runtime.DTSExecResult.Success;



                //Insert Flat File Connection
                ConnectionManager ConnMgrFlatFile = ssis.Connections.Add("FLATFILE");
                //You can change this path depending on where you have stored the flat file
                ConnMgrFlatFile.ConnectionString = xFullFilePath;// Dts.Connections["FlatFile"].ConnectionString;
                //Assign name to the flat file connection
                ConnMgrFlatFile.Name = FileName;
                //Indicate that the flat file is delimited
                ConnMgrFlatFile.Properties["Format"].SetValue(ConnMgrFlatFile, "Delimited");
                //Indicate whether the source file has column headings or not - in this case, our sample data has column headings.
                ConnMgrFlatFile.Properties["ColumnNamesInFirstDataRow"].SetValue(ConnMgrFlatFile, Convert.ToBoolean(true));
                ConnMgrFlatFile.Properties["CodePage"].SetValue(ConnMgrFlatFile, Convert.ToInt32(CodePage));

                //Get Native Flat File Connection
                RuntimeWrapper.IDTSConnectionManagerFlatFile100 rtwConnFlatFile = ConnMgrFlatFile.InnerObject as RuntimeWrapper.IDTSConnectionManagerFlatFile100;
                AddFlatFileColumns(ref rtwConnFlatFile, xFullFilePath, FileDelimite);

                //Add FlatFile Source Component
                //Insert Flat File source component
                PipelineWrapper.IDTSComponentMetaData100 componentSource = mDataFlow.ComponentMetaDataCollection.New();
                componentSource.Name = "FlatFileSource";
                componentSource.ComponentClassID = "{D23FD76B-F51D-420F-BBCB-19CBF6AC1AB4}";
                //Insert source design-time instance and initialise component
                PipelineWrapper.CManagedComponentWrapper instanceSource = componentSource.Instantiate();
                instanceSource.ProvideComponentProperties();
                //Set source connection
                componentSource.RuntimeConnectionCollection[0].ConnectionManagerID = ConnMgrFlatFile.ID;
                componentSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(ConnMgrFlatFile);


                //Reinitialize Flat File source metadata, 
                Reinitiaze(instanceSource);

                //Add RowCount Component
                PipelineWrapper.IDTSComponentMetaData100 icmdRowCount = AddComponentMetadata(mDataFlow, "RowCountComponent");
                icmdRowCount.ComponentClassID = "DTSTransform.RowCount";
                PipelineWrapper.CManagedComponentWrapper mcwRowCount = icmdRowCount.Instantiate();
                mcwRowCount.ProvideComponentProperties();
                icmdRowCount.Name = "TotalRecordCount";
                mcwRowCount.SetComponentProperty("VariableName", "User::intRowCount");

                //Join Path 
                PipelineWrapper.IDTSPath100 SourceToCount;

                SourceToCount = mDataFlow.PathCollection.New();
                SourceToCount.AttachPathAndPropagateNotifications(componentSource.OutputCollection[0], icmdRowCount.InputCollection[0]);
                Reinitiaze(mcwRowCount);

                //Add Paralle Distributor 
                PipelineWrapper.IDTSComponentMetaData100 icmdParallel = AddComponentMetadata(mDataFlow, "ParallelDistribution");
                icmdParallel.ComponentClassID = "{F06C606C-5980-460C-B2AF-B0ECA031B7A4}";
                PipelineWrapper.CManagedComponentWrapper mcwParallel = icmdParallel.Instantiate();
                mcwParallel.ProvideComponentProperties();


                PipelineWrapper.IDTSPath100 CountToDistributor;
                CountToDistributor = mDataFlow.PathCollection.New();
                CountToDistributor.AttachPathAndPropagateNotifications(icmdRowCount.OutputCollection[0], icmdParallel.InputCollection[0]);
                Reinitiaze(mcwParallel);




                //Add OLEDB Destination Componnent
                PipelineWrapper.IDTSComponentMetaData100 icmRawDestination = AddComponentMetadata(mDataFlow, "destination");
                icmRawDestination.ComponentClassID = "DTSAdapter.OLEDBDestination";
                icmRawDestination.ValidateExternalMetadata = true;

                PipelineWrapper.CManagedComponentWrapper mcwRawDestination = icmRawDestination.Instantiate();
                // tblDestination
                mcwRawDestination.ProvideComponentProperties();
                icmRawDestination.Name = "destination";
                icmRawDestination.Description = "destination";
                icmRawDestination.RuntimeConnectionCollection[0].ConnectionManagerID = destination.ID;
                icmRawDestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(destination);
                mcwRawDestination.SetComponentProperty("AccessMode", 3);//FastLoad
                mcwRawDestination.SetComponentProperty("OpenRowset", FillTableName);//tblDestination);
                mcwRawDestination.Validate();
                mcwRawDestination.SetComponentProperty("FastLoadKeepIdentity", true);
                mcwRawDestination.SetComponentProperty("FastLoadMaxInsertCommitSize", 10000);//FastLoadMaxCommitSize);
                mcwRawDestination.SetComponentProperty("FastLoadKeepNulls", false);
                mcwRawDestination.SetComponentProperty("FastLoadOptions", "TABLOCK,CHECK_CONSTRAINTS,ROWS_PER_BATCH = 50000");// + RowsPerBatch);
                Reinitiaze(mcwRawDestination);

                ///////////////////////////////////////////////////////
                //Add OLEDB Destination Componnent
                PipelineWrapper.IDTSComponentMetaData100 icmRawDestination2 = AddComponentMetadata(mDataFlow, "destination");
                icmRawDestination2.ComponentClassID = "DTSAdapter.OLEDBDestination";
                icmRawDestination2.ValidateExternalMetadata = true;

                PipelineWrapper.CManagedComponentWrapper mcwRawDestination2 = icmRawDestination2.Instantiate();
                // tblDestination
                mcwRawDestination2.ProvideComponentProperties();
                icmRawDestination2.Name = "destination2";
                icmRawDestination2.Description = "destination2";
                icmRawDestination2.RuntimeConnectionCollection[0].ConnectionManagerID = destination.ID;
                icmRawDestination2.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(destination);
                mcwRawDestination2.SetComponentProperty("AccessMode", 3);//FastLoad
                mcwRawDestination2.SetComponentProperty("OpenRowset", FillTableName);//tblDestination);
                mcwRawDestination2.Validate();
                mcwRawDestination2.SetComponentProperty("FastLoadKeepIdentity", true);
                mcwRawDestination2.SetComponentProperty("FastLoadMaxInsertCommitSize", 10000);//FastLoadMaxCommitSize);
                mcwRawDestination2.SetComponentProperty("FastLoadKeepNulls", false);
                mcwRawDestination2.SetComponentProperty("FastLoadOptions", "TABLOCK,CHECK_CONSTRAINTS,ROWS_PER_BATCH = 50000");// + RowsPerBatch);
                Reinitiaze(mcwRawDestination2);
                //////////////////////////////////////////////////////
                ///////////////////////////////////////////////////////
                //Add OLEDB Destination Componnent
                PipelineWrapper.IDTSComponentMetaData100 icmRawDestination3 = AddComponentMetadata(mDataFlow, "destination");
                icmRawDestination3.ComponentClassID = "DTSAdapter.OLEDBDestination";
                icmRawDestination3.ValidateExternalMetadata = true;

                PipelineWrapper.CManagedComponentWrapper mcwRawDestination3 = icmRawDestination3.Instantiate();
                // tblDestination
                mcwRawDestination3.ProvideComponentProperties();
                icmRawDestination3.Name = "destination3";
                icmRawDestination3.Description = "destination3";
                icmRawDestination3.RuntimeConnectionCollection[0].ConnectionManagerID = destination.ID;
                icmRawDestination3.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(destination);
                mcwRawDestination3.SetComponentProperty("AccessMode", 3);//FastLoad
                mcwRawDestination3.SetComponentProperty("OpenRowset", FillTableName);//tblDestination);
                mcwRawDestination3.Validate();
                mcwRawDestination3.SetComponentProperty("FastLoadKeepIdentity", true);
                mcwRawDestination3.SetComponentProperty("FastLoadMaxInsertCommitSize", 10000);//FastLoadMaxCommitSize);
                mcwRawDestination3.SetComponentProperty("FastLoadKeepNulls", false);
                mcwRawDestination3.SetComponentProperty("FastLoadOptions", "TABLOCK,CHECK_CONSTRAINTS,ROWS_PER_BATCH = 50000");// + RowsPerBatch);
                Reinitiaze(mcwRawDestination3);
                //////////////////////////////////////////////////////

                //join Source
                //TableLoading joins

                PipelineWrapper.IDTSPath100 CountToRawTable;
                CountToRawTable = mDataFlow.PathCollection.New();
                CountToRawTable.AttachPathAndPropagateNotifications(icmdParallel.OutputCollection[0], icmRawDestination.InputCollection[0]);

                PipelineWrapper.IDTSPath100 Distributor2Table;
                Distributor2Table = mDataFlow.PathCollection.New();
                Distributor2Table.AttachPathAndPropagateNotifications(icmdParallel.OutputCollection[1], icmRawDestination2.InputCollection[0]);

                PipelineWrapper.IDTSPath100 Distributor3Table;
                Distributor3Table = mDataFlow.PathCollection.New();
                Distributor3Table.AttachPathAndPropagateNotifications(icmdParallel.OutputCollection[2], icmRawDestination3.InputCollection[0]);


                MappColumns(icmRawDestination, mcwRawDestination);
                MappColumns(icmRawDestination2, mcwRawDestination2);
                MappColumns(icmRawDestination3, mcwRawDestination3);

                // Execute package

                SaveSSIS(@"\\c:\temp", SsisName);
                StoreSsisPackage(host);
                ssis.Execute();
                if ((ssis.ExecutionResult == DTSExecResult.Failure) || (ssis.ExecutionStatus == DTSExecStatus.Abend))
                {
                    StringBuilder ErrorMessage = new StringBuilder();


                    // Package Failure on Error 
                    vars["User::bolIsLoaded"].Value = false;
                    //Capture Error message 
                    foreach (Microsoft.SqlServer.Dts.Runtime.DtsError Error in ssis.Errors)
                    {
                        String ErrorDesc = Error.Source + ":" + Error.Description;
                        ErrorMessage.Append(ErrorDesc);
                        ErrorDesc = null;

                    }
                    String eMessage = ErrorMessage.ToString();
                    vars["User::ErrorMessage"].Value = eMessage;
                    ErrorMessage = null;
                    throw new System.ArgumentException("Loading RawData File Error ", eMessage);




                }
                else
                {
                    vars["User::intRecordCount"].Value = intRowCount.Value;
                    //vars["User::bolIsLoaded"].Value = true;
                    // SaveSSIS(SaveDirectory, myPackage.Name.ToString());
                    vars.Unlock();
                    FilePattern = null;
                    ssis.Dispose();


                    Dts.TaskResult = (int)ScriptResults.Success;
                }

            }
            catch (ArgumentException e)
            {
                // MessageBox.Show(e.Message.ToString());

              //  StoreSsisPackage(host);
                ssis.Name = "Error" + ssis.Name.ToString();
                
                SaveSSIS(@"\\c\temp\ssis", ssis.Name.ToString());
                ssis.Dispose();
                throw e;


            }
           
           
        }
        // save XML package into errorlog table
        private void StoreSsisPackage(ConnectionManager host)
        {
            int LogId = (int)vars["User::intETLLogId"].Value;
            StringBuilder strbSQLCmd = new StringBuilder();
            XmlDocument xlmdoc = new XmlDocument();
            String ServerName = (String)host.Properties["ServerName"].GetValue(host);
            String DatabaseName = (string)host.Properties["InitialCatalog"].GetValue(host);
            String StoreProc = "Log.SsisStorage_InsUpd";

            ssis.SaveToXML(ref xlmdoc, null, null);
            strbSQLCmd.Append("Server=");
            strbSQLCmd.Append(ServerName);
            strbSQLCmd.Append(";Database=");
            strbSQLCmd.Append(DatabaseName);
            strbSQLCmd.Append(";Trusted_Connection=TRUE");

            SqlConnection sqlCon = new SqlConnection(strbSQLCmd.ToString());
            using (DbCommand Cmd = sqlCon.CreateCommand())
            {
                Cmd.CommandType =  System.Data.CommandType.StoredProcedure;
                Cmd.CommandText = StoreProc;
                Cmd.Parameters.Add(new SqlParameter("@ETLLogId", System.Data.SqlDbType.Int)
                {
                    Value = LogId
                });
                Cmd.Parameters.Add(new SqlParameter("@PackageName", System.Data.SqlDbType.VarChar)
                {
                    Value = ssis.Name.ToString()
                });

                Cmd.Parameters.Add(new SqlParameter("@pkg", System.Data.SqlDbType.Xml)
                {
                   Value = new System.Data.SqlTypes.SqlXml(new XmlTextReader(xlmdoc.InnerXml,XmlNodeType.Document,null))
                });
                sqlCon.Open();
                DbTransaction tran = sqlCon.BeginTransaction();
                Cmd.Transaction = tran;
                try
                {
                    String strCmd = Cmd.CommandText.ToString();
                    Cmd.ExecuteNonQuery();

                    tran.Commit();
                    sqlCon.Close();
                }
                catch (Exception e)
                {
                   
                   MessageBox.Show(e.ToString());
                    tran.Rollback();
                    sqlCon.Close();
                    throw;
                }
            }
                                 

        }

        /*
     *  Add Destinatin Componnet
     */
        private PipelineWrapper.IDTSComponentMetaData100 AddComponentMetadata(PipelineWrapper.MainPipe wf, String ComponentName)
        {
            PipelineWrapper.IDTSComponentMetaData100 icmd = wf.ComponentMetaDataCollection.New();
            return icmd;
        }
        private void AddFlatFileColumns(ref RuntimeWrapper.IDTSConnectionManagerFlatFile100 ConnMgrFlatFile, String FullFilePath, String FileDelimited)
        {
            String line;
            //Determine the number of columns by reading the sample Flat File - line by line.            
            using (StreamReader file = new StreamReader(FullFilePath))
            {
                try
                {

                    while ((line = file.ReadLine()) != null)
                    {
                        char[] delimiters = FileDelimited.ToCharArray();
                        string[] parts = line.Split(delimiters, StringSplitOptions.RemoveEmptyEntries);

                        for (int i = 0; i < parts.Length; i++)
                        {
                            RuntimeWrapper.IDTSConnectionManagerFlatFileColumn100 flatFileCol = ConnMgrFlatFile.Columns.Add() as RuntimeWrapper.IDTSConnectionManagerFlatFileColumn100;
                            sS_AssignColumnProperties(flatFileCol, parts[i], FileDelimited);
                        }
                        //Exit file after reading the first line
                        break;
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    file.Close();
                }
            }
            //Edit the last Flat File column delimiter into NewLine instead of a Comma
            ConnMgrFlatFile.Columns[ConnMgrFlatFile.Columns.Count - 1].ColumnDelimiter = Environment.NewLine;

        }
        private static void sS_AssignColumnProperties(RuntimeWrapper.IDTSConnectionManagerFlatFileColumn100 flatFileCol, string getColName, string getDelim)
        {
            //Assign delimiter
            flatFileCol.ColumnType = "Delimited";
            flatFileCol.ColumnDelimiter = getDelim;
            

            //Indicate column data type - in this case, all the source columns will be set to String Data Type
            flatFileCol.DataType = RuntimeWrapper.DataType.DT_STR;
            //Indicate column width - in this case, width of all source columns will be set to a length of 800
            flatFileCol.ColumnWidth = 8000;
            flatFileCol.MaximumWidth = 8000;
           
            

            //Assign column name
            RuntimeWrapper.IDTSName100 columnName = flatFileCol as RuntimeWrapper.IDTSName100;
            columnName.Name = getColName.ToString();
        }
        //Save Ssis package to a location
        private void SaveSSIS(String Directory, String PackageName)
        {
            String FullFilePath = Directory + "\\" + PackageName + ".dtsx";
           app.SaveToXml(FullFilePath, ssis, null);
        }

         private void AddLogging(String ConnectionName, Boolean Enable)
        {
            LogProvider pkLogging;
            pkLogging = ssis.LogProviders.Add("DTS.LogProviderSQLServer");
            pkLogging.Name = "Log Provider For SQL SERVER";
            pkLogging.Description = "Log Provider For SQL SERVER";
            pkLogging.ConfigString = ConnectionName;
            ssis.LoggingOptions.SelectedLogProviders.Add(pkLogging);
            ssis.LoggingOptions.EventFilterKind = Microsoft.SqlServer.Dts.Runtime.DTSEventFilterKind.Inclusion;
            ssis.LoggingOptions.EventFilter = new String[] { "OnPreExecute", "OnPostExecute", "OnError" };
            switch (Enable)
            {
                case true:
                    ssis.LoggingMode = Microsoft.SqlServer.Dts.Runtime.DTSLoggingMode.Enabled;
                    break;
                case false:
                    ssis.LoggingMode = Microsoft.SqlServer.Dts.Runtime.DTSLoggingMode.Disabled;
                    break;
                default:
                    ssis.LoggingMode = Microsoft.SqlServer.Dts.Runtime.DTSLoggingMode.Disabled;
                    break;

            }
        }
        /*
         * Reinitiazied
         */
        private void Reinitiaze(PipelineWrapper.CManagedComponentWrapper InstanceSource)
        {
            //Reinitialize Flat File source metadata,
            InstanceSource.AcquireConnections(null);
            InstanceSource.ReinitializeMetaData();
            InstanceSource.ReleaseConnections();
        }
         /*
         * Add OLEDB Connection
         */
        public ConnectionManager AddOLEDBConnection(String ConnectionName, String ConnectionStr)
        {
            ConnectionManager ConMgr = ssis.Connections.Add("OLEDB");

            ConMgr.ConnectionString = ConnectionStr + "Packet Size=32076;";
            ConMgr.Name = ConnectionName;
            ConMgr.Description = "SQL OLEDB using " + ConnectionName;

            return ConMgr;

        }
        /*
         * Add WorkFlow
         *
         */
        private PipelineWrapper.MainPipe AddWorkFlowComponent(String ComponentName)
        {
            ssis.Executables.Add("STOCK:PipelineTask");
            TaskHost _TaskHost = (TaskHost)ssis.Executables[0];
            PipelineWrapper.MainPipe dataFlowTask = (PipelineWrapper.MainPipe)_TaskHost.InnerObject;
            _TaskHost.Name = ComponentName;
            _TaskHost.Properties["DefaultBufferMaxRows"].SetValue(_TaskHost, "1000000");

            return dataFlowTask;

        }
     
        // Create SSIS executable
        private Executable AddSQLExecutable(String MONIKER)
        {
            Executable exec = ssis.Executables.Add(MONIKER);

            return exec;
        }

        /* need to work on this if more than one file then error or what to do*/
        private String GetFileName(String DirectoryPath, String NamePattern)
        {
            String[] Files = System.IO.Directory.GetFiles(DirectoryPath, NamePattern);
            String FullPath = "";
            foreach (String file in Files)
            {
               FullPath = System.IO.Path.GetFullPath(file);
            }
            return FullPath;
        }
        private void AddSQLTask(Executable exec, String TaskName, String Connection, String SqlCmd)
        {
            Runtime.TaskHost sqlTaskHost = (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);

            SQLTask.IDTSExecuteSQL iexecFillRawSqlTask = (SQLTask.IDTSExecuteSQL)sqlTaskHost.InnerObject;
            iexecFillRawSqlTask.ResultSetType = SQLTask.ResultSetType.ResultSetType_None;
            iexecFillRawSqlTask.SqlStatementSourceType = SQLTask.SqlStatementSourceType.DirectInput;


        }
       
        private void MappColumns(PipelineWrapper.IDTSComponentMetaData100 icmDest, PipelineWrapper.CManagedComponentWrapper mcwDest)
        {
            //mappings

            PipelineWrapper.IDTSInput100 inpDestination = icmDest.InputCollection[0];
            PipelineWrapper.IDTSVirtualInput100 vinpDestination = inpDestination.GetVirtualInput();
            PipelineWrapper.IDTSVirtualInputColumnCollection100 vinpcDestination = (PipelineWrapper.IDTSVirtualInputColumnCollection100)vinpDestination.VirtualInputColumnCollection;


            foreach (PipelineWrapper.IDTSVirtualInputColumn100 vcolumn in vinpDestination.VirtualInputColumnCollection)
            {

                try
                {
                    PipelineWrapper.IDTSInputColumn100 inputColumn = mcwDest.SetUsageType(inpDestination.ID, vinpDestination, vcolumn.LineageID, PipelineWrapper.DTSUsageType.UT_READONLY);
                    // map external column
                    // inputColumn.Name = inputColumn.Name.Replace("[", "").Replace("]", "");
                    PipelineWrapper.IDTSExternalMetadataColumn100 extColumn = inpDestination.ExternalMetadataColumnCollection[inputColumn.Name];

                    mcwDest.MapInputColumn(inpDestination.ID, inputColumn.ID, extColumn.ID);

                }
                catch
                {
                    PipelineWrapper.IDTSInputColumn100 inputColumn = mcwDest.SetUsageType(inpDestination.ID, vinpDestination, vcolumn.LineageID, PipelineWrapper.DTSUsageType.UT_IGNORED);

                    // map external column

                }

            }


        }
        

        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        /// 
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

}
}

Contact Form

Name

Email *

Message *