SQLSaturday 168 Re-Cap

I have a very great time in SQLSaturday 168, this was my first trip to Tampa Florida and I did have a great time there. The hotel was just a few blocks from the city ybor and it was amazing.

I enjoy some great sessions and I miss the session by Chad about sql replication which I did really wanted to see. We only have around 45 minutes during presentations which is not a lot of time,but, is better than nothing :-).

Finally here is the script code for the dynamic ssis, my session was about files, however, I spend too much time on the details of a proper ETL work flow methodology.

Script to Create an SSIS Package to load a File into a Table.

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using RuntimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.IO;

namespace ST_94a9f0913c0a487aa85d3139c2ca8c59.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion
        public void Main()
        {
            //Declare new aplication
                Application sqlSaturday168_app = new Application();

                //Create package
                Package ssisFile2Table = new Package();
                String PackageStorage = @"C:\\temp\\SQLSaturday168\\AutoPackage";
            try
            {
                //User::strDestDBName,User::strDestinationServer,User::strDestSchema,User::strDestTable,User::strFileDelimited,User::strFileDirectory,User::strFileName
                String DatabaseName = Dts.Variables["User::strDestDBName"].Value.ToString();
                String DatabaseSchema = Dts.Variables["User::strDestSchema"].Value.ToString();
                String TableName = DatabaseSchema+"."+Dts.Variables["User::strDestTable"].Value.ToString();
                String FileDelimited = Dts.Variables["User::strFileDelimited"].Value.ToString();
                String FileDirectory = Dts.Variables["User::strFileDirectory"].Value.ToString();
                String FileName = Dts.Variables["User::strFileName"].Value.ToString();
                String Servername = Dts.Variables["User::strDestinationServer"].Value.ToString();
              

                String FullFilePath = FileDirectory + "\\" + FileName;

                //Assign relevant package name and description
                ssisFile2Table.Name = "SQLSaturday168 Demo Using C#";
                ssisFile2Table.Description = "Programmatically create an SSIS 2008 package that loads a Flat File Source into OLE DB Destination Using Script Task's C# language";

                //Insert the Data Flow Task with appropriate name and some buffer space for processing of file           
                ssisFile2Table.Executables.Add("STOCK:PipelineTask");
                TaskHost taskHost = ssisFile2Table.Executables[0] as TaskHost;
                MainPipe dataFlowTask = (MainPipe)taskHost.InnerObject;
                taskHost.Name = "Dynamic Data Flow Task";
                taskHost.Properties["DefaultBufferMaxRows"].SetValue(taskHost, "1000000");

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

                //Get native Flat File connection
                RuntimeWrapper.IDTSConnectionManagerFlatFile100 connectionFlatFile = connectionManagerFlatFile.InnerObject as RuntimeWrapper.IDTSConnectionManagerFlatFile100;

                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 = connectionFlatFile.Columns.Add() as RuntimeWrapper.IDTSConnectionManagerFlatFileColumn100;
                                sS_AssignColumnProperties(flatFileCol, parts[i], "|");
                            }
                            //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
                connectionFlatFile.Columns[connectionFlatFile.Columns.Count - 1].ColumnDelimiter = Environment.NewLine;

                //Insert Flat File source component
                IDTSComponentMetaData100 componentSource = dataFlowTask.ComponentMetaDataCollection.New();
                componentSource.Name = "FlatFileSource";
                componentSource.ComponentClassID = "DTSAdapter.FlatFileSource.2";

                //Insert source design-time instance and initialise component
                CManagedComponentWrapper instanceSource = componentSource.Instantiate();
                instanceSource.ProvideComponentProperties();

                //Set source connection
                componentSource.RuntimeConnectionCollection[0].ConnectionManagerID = connectionManagerFlatFile.ID;
                componentSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(connectionManagerFlatFile);

                //Reinitialize Flat File source metadata,
                instanceSource.AcquireConnections(null);
                instanceSource.ReinitializeMetaData();
                instanceSource.ReleaseConnections();

                //Insert the SQL Server 2008 OLE-DB connection
                String ConnectionString = "Data Source=" + Servername + ";Initial Catalog=" + DatabaseName + ";Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;";
                ConnectionManager connectionManagerOleDb = ssisFile2Table.Connections.Add("OLEDB");
                connectionManagerOleDb.ConnectionString = string.Format(ConnectionString);
                connectionManagerOleDb.Name = "OLEDB";
                connectionManagerOleDb.Description = "OLEDB Connection";

                //Insert OLE-DB destination
                IDTSComponentMetaData100 componentDestination = dataFlowTask.ComponentMetaDataCollection.New();
                componentDestination.Name = "OLEDBDestination";
                componentDestination.Description = "OLEDB Destination for the Flat File data load";
                componentDestination.ComponentClassID = "DTSAdapter.OLEDBDestination.2";

                //Insert destination design-time instance and initialise component
                CManagedComponentWrapper instanceDestination = componentDestination.Instantiate();
                instanceDestination.ProvideComponentProperties();

                //Set destination connection
                componentDestination.RuntimeConnectionCollection[0].ConnectionManagerID = connectionManagerOleDb.ID;
                componentDestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(connectionManagerOleDb);
                //Indicates the name of the database object used to open a rowset
                instanceDestination.SetComponentProperty("OpenRowset", TableName);
                //Specifies the mode used to open the database
                instanceDestination.SetComponentProperty("AccessMode", 3);
                //Specifies options to be used with fast load. Applies only if fast load is turned on
                instanceDestination.SetComponentProperty("FastLoadOptions", "TABLOCK,CHECK_CONSTRAINTS");
                //Indicates whether the values supplied for identity columns will be copied to the destination or not
                //In this case, we have set this property to false
                instanceDestination.SetComponentProperty("FastLoadKeepIdentity", false);
                //Indicates whether the columns containing null willhave null inserted in the destination or not
                //In this case, we have opted no to insert nulls
                instanceDestination.SetComponentProperty("FastLoadKeepNulls", false);
                //Specifies the column code page to use when code page information is unavailable from the data source
                //In this case we used the default - 1252
                instanceDestination.SetComponentProperty("DefaultCodePage", 1252);
                //Specifies when commits are issued during data insertion
                //In this case, we have opted for the default size which is set to 2147483647
                instanceDestination.SetComponentProperty("FastLoadMaxInsertCommitSize", 2147483647);
                //Indicates the number of seconds before a command times out
                //In this case, we have opted for the default value of 0 which indicates an infinite time-out
                instanceDestination.SetComponentProperty("CommandTimeout", 0);
                //Indicates the usage of DefaultCodePage property value when describing the character data
                //In this case, we have opted for the default value of false
                instanceDestination.SetComponentProperty("AlwaysUseDefaultCodePage", false);

                //Connect the Flat File source to the OLE DB Destination component
                dataFlowTask.PathCollection.New().AttachPathAndPropagateNotifications(componentSource.OutputCollection[0], componentDestination.InputCollection[0]);

                //Get input and virtual input for destination to select and map columns
                IDTSInput100 destinationInput = componentDestination.InputCollection[0];
                IDTSVirtualInput100 destinationVirtualInput = destinationInput.GetVirtualInput();
                IDTSVirtualInputColumnCollection100 destinationVirtualInputColumns = destinationVirtualInput.VirtualInputColumnCollection;

                //Reinitialize the metadata, generating exernal columns from flat file columns
                instanceDestination.AcquireConnections(null);
                instanceDestination.ReinitializeMetaData();
                instanceDestination.ReleaseConnections();

                //Select and map destination columns
                foreach (IDTSVirtualInputColumn100 virtualInputColumn in destinationVirtualInputColumns)
                {
                    // Select column, and retain new input column
                    IDTSInputColumn100 inputColumn = instanceDestination.SetUsageType(destinationInput.ID, destinationVirtualInput, virtualInputColumn.LineageID, DTSUsageType.UT_READONLY);
                    // Find external column by name
                    IDTSExternalMetadataColumn100 externalColumn = destinationInput.ExternalMetadataColumnCollection[inputColumn.Name];
                    // Map input column to external column
                    instanceDestination.MapInputColumn(destinationInput.ID, inputColumn.ID, externalColumn.ID);
                }

                //Execute the package or disable the below code if you intend running the package later
               // ssisFile2Table.Execute();

                //Finally, save the package - in this case, we have opted to save the package into file system
                sqlSaturday168_app.SaveToXml(PackageStorage+"\\"+Dts.Variables["User::strDestTable"].Value.ToString()+".dtsx", ssisFile2Table, null);

                Dts.TaskResult = (int)ScriptResults.Success;
            }
            catch
            {
                sqlSaturday168_app.SaveToXml(PackageStorage+"\\ERROR_"+Dts.Variables["User::strDestTable"].Value.ToString()+".dtsx", ssisFile2Table, null);
                Dts.TaskResult = (int)ScriptResults.Failure;
            }
        }

        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 100
            flatFileCol.ColumnWidth = 100;

            //Assign column name
            RuntimeWrapper.IDTSName100 columnName = flatFileCol as RuntimeWrapper.IDTSName100;
            columnName.Name = getColName.ToString();
        }
    }
}

SQLSATURDAY 168 Presentation

SQLSATURDAY #168 Tampa

 Congratulations I just got selected as a speaker for SQLSaturday #168 BI Edition in Tampa, Florida SQLSaturday #168, Complex File Structures HMM...let see some many possibilities 

SSIS Reverse Class Modifications

Tonight is the premier of the movie of the Dark Night the previous are crazy and the ticket sale will start in a few minutes and I will be heading to the movies in a few minutes, but, before that here is the latest code of the reverse ssis

  

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using System.IO;

namespace jnovo.ETL.ssisManagement.Prototype
{
    public class ssisReverse
    {
        ssisPackage _package;



        Boolean PackageExists = false;
        public const string PackageCreationName = "Package";
        public const string EventHandlerCreationName = "EventHandler";
        public const string ConnectionCreationName = "Connection";
        public const string SequenceCreationName = "Sequence";
        public const string ForLoopCreationName = "ForLoop";
        public const string ForEachLoopCreationName = "ForEachLoop";
        #region constructor

        #endregion
        #region public properties
        public ssisReverse(String PackageDirectoryPath, String PackageName)
        {
            String FullFilePath = PackageDirectoryPath + "\\" + PackageName + ".dtsx";
            if (File.Exists(FullFilePath))
            {
                _package = new ssisPackage(FullFilePath);
                PackageExists = true;

            }
            else
            {
                Console.WriteLine("Error: {0} Package Not found", FullFilePath);
                PackageExists = false;

            }
            if (PackageExists)
            {

            }
        }
        #endregion
        #region public sub modules

        public void DisplayResults()
        {
            if (PackageExists)
            {
                Console.WriteLine("Searching Package: {0}", _package.PackageName);
                GetConnections();
                SearchPackage(_package.getExecutables);

            }
            else
            {
                Console.WriteLine("Errors with the Package or Path please validate");
            }
        }

        #endregion

        #region private sub modules
        // Search Packge Module to find Components

        private void SearchPackage(Executables CollExec)
        {
            int count = 0;
            int count2 = (CollExec.Count);
            while (count != (count2))

            // foreach (Executable exc in CollExec)
            {

                Executable exc = CollExec[count++];
                switch (exc.GetType().Name)
                {
                    case "TaskHost":
                        TaskHost th = (TaskHost)exc;
                        String Desc = th.CreationName;
                        int idx = Desc.IndexOf(",");
                        if (idx > 0)
                        {

                            Desc = Desc.Substring(0, idx);
                            Desc = Desc.Substring(Desc.LastIndexOf(".") + 1);

                        }

                        Console.WriteLine("TaskHost - {0} TaskHostType - {1}", th.Name, Desc);
                        if (Desc.CompareTo("SSIS.Pipeline.2") == 0)
                        {
                            GetTaskHost(th);
                        }


                        // DtsContainer e = (DtsContainer)exc;
                        // GetContainerType(e);

                        GetEventHandlers(th.EventHandlers);


                        th = null;
                        continue;
                    case "Sequence":
                        Sequence seq = (Sequence)exc;
                        Console.WriteLine("Sequence Container - {0}", seq.Name);
                        //  GetPrecedence(seq.PrecedenceConstraints);
                        SearchPackage(seq.Executables);
                        GetEventHandlers(seq.EventHandlers);

                        // seq = null;
                        continue;
                    case "ForEachLoop":
                        ForEachLoop fel = (ForEachLoop)exc;
                        Console.WriteLine("ForEachLoop Container - {0}", fel.Name);
                        SearchPackage(fel.Executables);
                        GetEventHandlers(fel.EventHandlers);
                        //   GetPrecedence(fel.PrecedenceConstraints);
                        fel = null;
                        continue;
                    case "ForLoop":
                        ForLoop fl = (ForLoop)exc;
                        Console.WriteLine("ForLoop Container - {0}", fl.Name);
                        SearchPackage(fl.Executables);
                        GetEventHandlers(fl.EventHandlers);
                        //    GetPrecedence(fl.PrecedenceConstraints);
                        fl = null;
                        continue;

                }

            }

        }


        // Recursive to find evenths
        private void GetContainerType(DtsContainer e)
        {
            Console.WriteLine("TaskHostContainer: {0}", e.GetType().ToString());
        }
        private void GetEventHandlers(DtsEventHandlers colleventh)
        {
            foreach (DtsEventHandler eventh in colleventh)
            {
                SearchPackage(eventh.Executables);
            }
        }
        public void GetComponentInfos()
        {
            Application app = _package.getApplication;
            PipelineComponentInfos cinfos = app.PipelineComponentInfos;
            foreach (PipelineComponentInfo componentInfo in cinfos)
            {

                Console.WriteLine("\"" + componentInfo.Name + "\"" + "," + "\"" + componentInfo.CreationName + "\"");
            }
            Console.Read();
        }
        // Find dataflows
        private void GetTaskHost(TaskHost th)
        {
            if (th == null)
            {
                return;
            }


            Console.WriteLine("TaskHostType--> {0}", th.CreationName);
            MainPipe pipeline = th.InnerObject as MainPipe;
            if (pipeline != null)
            {

                GetPipeline(th, pipeline);
                getPathCollections(pipeline.PathCollection);
            }
        }
        //SQL 2005
        private void GetPipeline(TaskHost th, MainPipe pipe)
        {


            foreach (IDTSComponentMetaData100 componentMetadata in pipe.ComponentMetaDataCollection)
            {


                Console.WriteLine("DataFlow: - {0} Component :{1} ", th.Name.ToString(), componentMetadata.Name);



            }
        }
        private void GetPrecedence(PrecedenceConstraints precedenceconstraints)
        {
            foreach (PrecedenceConstraint pre in precedenceconstraints)
            {

                Console.WriteLine("PrecedenceConstrain: {0}", pre.Name);
                Console.WriteLine("From  {0}", pre.PrecedenceExecutable.ToString());
                Console.WriteLine("To {0}", pre.ConstrainedExecutable.ToString());
            }
        }
        private void getPathCollections(IDTSPathCollection100 path)
        {
            foreach (IDTSPath100 p in path)
            {
                Console.WriteLine("DataFlow OutPutPaths: {0} TO {1}", p.StartPoint.Component.Name.ToString(), p.EndPoint.Component.Name.ToString());
            }
        }
        private void GetConnections()
        {
            foreach (ConnectionManager con in _package.getConnections)
            {
                Console.WriteLine("Connections: {0}", con.Name);
            }
        }

        #endregion

    }
}

SSIS 2005 File To Table Script

Today the Client Extraction team has a little problem. From each type of file which can have a different delimiter and text qualifier they need a simple package to load each file for validation,
after a few questions and searching in my old bag of treats I found some old code that was doing just that. I put a simple prototype, slap some some variables and wrote what I like to call ugly code
but it got the concept and work done. Basically the package

generate an ssis with a single file connection with the proper delimiter and data type, quick and dirty but it got the job done.The code below is not perfect but it would be a start for anyone who wants to implement the same concept...Ah the work was done for sql 2005...Its 2012 please Update....Cheers

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports System.Data.SqlClient
Imports System.IO





<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum

    ' The execution engine calls this method when the task executes.
    ' To access the object model, use the Dts object. Connections, variables, events,
    ' and logging features are available as static members of the Dts class.
    ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    '
    ' To open Code and Text Editor Help, press F1.
    ' To open Object Browser, press Ctrl+Alt+J.

    Public Sub Main()
        Dim pkg As Microsoft.SqlServer.Dts.Runtime.Package = New Microsoft.SqlServer.Dts.Runtime.Package

        Try
            'package variables
            pkg.Variables.Add("FileRowCount", False, "User", -1)



            Dim Flatcon As String = Dts.Variables("User::strFullFilePath").Value.ToString()

            Dim DestinationTable As String = Dts.Variables("User::strDestinationTableName").Value.ToString


            Dim FlatFileCompName As String = "FlatFileSource"
            Dim PackageName As String = "ImportClient_ImportClient"
            Dim SqlCmd As String = "select c.name,c.system_type_id as xtype,c.max_length as [Length], c.scale,c.precision from sys.columns c " _
                    + "where object_id = object_id(N'" _
                    + DestinationTable + "') and OBJECTPROPERTY (object_id,'IsTable')= 1"

            ' Create ssis Package

            pkg.Name = PackageName
            Dim FileDelimiter As String = Dts.Variables("User::strFileDelimiter").Value.ToString
            Dim DestinationContStr As String = Dts.Variables("User::strDestConStr").Value.ToString
            Dim TargetTable As String = DestinationTable


            'File properties'
            Dim ConMgr As ConnectionManager = CreatFileConnection(pkg)
            'Dim ConMgr As ConnectionManager = pkg.Connections.Add("FLATFILE")
            ConMgr.ConnectionString = Flatcon

            'Get native Flat File Connection
            Dim myFlatFileConn As IDTSConnectionManagerFlatFile100
            myFlatFileConn = CType(ConMgr.InnerObject, IDTSConnectionManagerFlatFile100)


            'Connect to SQL Server
            Dim mySqlConn As New System.Data.SqlClient.SqlConnection
            Dim SqlConnContStr As String = "Server=;Database=;Trusted_Connection=True;"
            mySqlConn.ConnectionString = SqlConnContStr

            'mySqlConn.ConnectionString = SqlConnContStr
            Dim mySqlCommand As New SqlCommand(SqlCmd, mySqlConn)
            mySqlConn.Open()



            Dim reader As SqlDataReader = mySqlCommand.ExecuteReader()

            While reader.Read
                Dim colName As String
                Dim colidx As Int16
                Dim col As IDTSConnectionManagerFlatFileColumn100 = CreateColumnProperties(myFlatFileConn)
                colName = DirectCast(col, IDTSName100).Name.ToString
                colidx = Convert.ToInt16(colName.Replace("col", ""))
                colName = DirectCast(reader.GetValue(0), String)
                SetDtsColumnProperties(col, reader)
                Dim ColumnName As IDTSName100 = DirectCast(col, IDTSName100)
                ColumnName.Name = colName
            End While

            ' Check column count
            If myFlatFileConn.Columns.Count = 0 Then
                Throw New ArgumentException(String.Format("No flat file columns have been created Check that destination table exists"))
            End If
            ' Correct last Flat File column delimiter
            myFlatFileConn.Columns(myFlatFileConn.Columns.Count - 1).ColumnDelimiter = Environment.NewLine

            'Add SQL OLE-DB Conn
            Dim DbConn As ConnectionManager = pkg.Connections.Add("OLEDB")
            DbConn.ConnectionString = Dts.Connections("host").ConnectionString
            DbConn.Name = DestinationTable

            ' Dim DbConn As ConnectionManager = CreateOLEDBConnection(pkg, "host")'
            Dim MSDBCON As ConnectionManager = CreateOLEDBConnection(pkg, "msdb")
            MSDBCON.ConnectionString = Dts.Connections("msdb").ConnectionString


            'Loging Errors
            Dim PackageLoging As LogProvider = pkg.LogProviders.Add("DTS.LogProviderSQLServer.1")
            PackageLoging.ConfigString = MSDBCON.Name

            pkg.LoggingOptions.SelectedLogProviders.Add(PackageLoging)
            pkg.LoggingOptions.EventFilterKind = Microsoft.SqlServer.Dts.Runtime.DTSEventFilterKind.Inclusion
            pkg.LoggingOptions.EventFilter = New String() {"OnPreExecute", "OnPostExecute", "OnError"}
            pkg.LoggingMode = Microsoft.SqlServer.Dts.Runtime.DTSLoggingMode.Disabled


            'Add Data Flow Task
            Dim e As Executable = pkg.Executables.Add("STOCK:PipelineTask")

            'Get Task host Wrapper and Data Flow Task
            Dim TaskHost As Microsoft.SqlServer.Dts.Runtime.TaskHost = CType(e, Microsoft.SqlServer.Dts.Runtime.TaskHost)
            TaskHost.Name = "DataFlow" 'Dts.Variables("User::vn_strClientName").Value.ToString + "_ImportFile"
            Dim dataFlowTask As MainPipe = CType(TaskHost.InnerObject, MainPipe)

            'Add Flat File Component
            Dim FlatFileComponent As IDTSComponentMetaData100 = dataFlowTask.ComponentMetaDataCollection.[New]

            FlatFileComponent.Name = "FlatFileSource"
            FlatFileComponent.ComponentClassID = "DTSAdapter.FlatFileSource.1"

            'Add Row count
            Dim RowCountComponent As IDTSComponentMetaData100 = dataFlowTask.ComponentMetaDataCollection.[New]
            RowCountComponent.ComponentClassID = "DTSTransform.RowCount.1"

            Dim rowInstance As CManagedComponentWrapper = RowCountComponent.Instantiate
            rowInstance.ProvideComponentProperties()
            rowInstance.SetComponentProperty("VariableName", "User::FileRowCount")


            'Get Source Design-time instance, and show time
            Dim InstanceSource As CManagedComponentWrapper = FlatFileComponent.Instantiate
            InstanceSource.ProvideComponentProperties()

       


            ''''''''''''''''''''''''''''''''''''''''''''''''''

            ' Set source connection

            FlatFileComponent.RuntimeConnectionCollection(0).ConnectionManagerID = ConMgr.ID
            FlatFileComponent.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(ConMgr)

            ' Set the source properties, optional, we use the extra FileNameColumnName column property
            ' InstanceSource.SetComponentProperty("FileNameColumnName", "FileName")

            'Reinitialize the metadata,
            InstanceSource.AcquireConnections(vbNull)
            InstanceSource.ReinitializeMetaData()
            InstanceSource.ReleaseConnections()



            ' Add OLE-DB destination
            Dim componentDestination As IDTSComponentMetaData100 = dataFlowTask.ComponentMetaDataCollection.New()
            componentDestination.Name = "OLEDBDestination"
            componentDestination.ComponentClassID = "DTSAdapter.OLEDBDestination.1" '"DTSAdapter.OLEDBDestination.1"
            componentDestination.ValidateExternalMetadata = False

            ' Get destination design-time instance, and initialise component
            Dim instanceDestination As CManagedComponentWrapper = componentDestination.Instantiate()
            instanceDestination.ProvideComponentProperties()

            ' Set destination connection
            componentDestination.RuntimeConnectionCollection(0).ConnectionManagerID = DbConn.ID
            componentDestination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(DbConn)

            'Set destination table name
            instanceDestination.SetComponentProperty("OpenRowset", TargetTable)

            ' Get input and virtual input for destination to select and map columns
            ' FlatFile to RowCount
            Dim path1 As IDTSPath100 = dataFlowTask.PathCollection.[New]
            path1.AttachPathAndPropagateNotifications(FlatFileComponent.OutputCollection(0), RowCountComponent.InputCollection(0))


            ''RowCount to Derive Column
            'Dim path As IDTSPath100 = dataFlowTask.PathCollection.New()
            'path.AttachPathAndPropagateNotifications(RowCountComponent.OutputCollection(0), DerivedColumn.InputCollection(0))

            'Derive Column To Destination
            Dim path3 As IDTSPath100 = dataFlowTask.PathCollection.[New]
            path3.AttachPathAndPropagateNotifications(RowCountComponent.OutputCollection(0), componentDestination.InputCollection(0))

        

            'Create the input columns for the transformation component
            Dim input As IDTSInput100 = RowCountComponent.InputCollection(0)
            Dim derivedInputVirtual As IDTSVirtualInput100 = input.GetVirtualInput()
            input.ErrorRowDisposition = DTSRowDisposition.RD_NotUsed
            input.ErrorOrTruncationOperation = ""
            rowInstance.ReleaseConnections()

            Dim destinationInput As IDTSInput100 = componentDestination.InputCollection(0)
            ' Dim destinationInputId = Input.ID as strin
            Dim destinationVirtualInput As IDTSVirtualInput100 = destinationInput.GetVirtualInput
            Dim destinationVirtualInputColumns As IDTSVirtualInputColumnCollection100 = CType(destinationVirtualInput.VirtualInputColumnCollection, IDTSVirtualInputColumnCollection100)

            ' Reinitialize the metadata, generating exernal columns from flat file columns
            ' If errors are raised here, it is most likely because the flat file connection columns
            ' are wrong, which itself is probably because the template table does not match the file.
            instanceDestination.AcquireConnections(vbNull)
            instanceDestination.ReinitializeMetaData()
            instanceDestination.ReleaseConnections()
            ' Save package to disk, DEBUG only

            For Each vcolumn As IDTSVirtualInputColumn100 In destinationVirtualInput.VirtualInputColumnCollection
                Dim inputColumn As IDTSInputColumn100 = instanceDestination.SetUsageType(destinationInput.ID, destinationVirtualInput, vcolumn.LineageID, DTSUsageType.UT_READONLY)
                Dim externalColumn As IDTSExternalMetadataColumn100 = destinationInput.ExternalMetadataColumnCollection(inputColumn.Name)
                instanceDestination.MapInputColumn(destinationInput.ID, inputColumn.ID, externalColumn.ID)
            Next


            'Execute Package'
            Dim App As Microsoft.SqlServer.Dts.Runtime.Application = New Microsoft.SqlServer.Dts.Runtime.Application
            App.SaveToXml(String.Format("C:\Temp\ssis_test_" + Format(Now(), "MMddyyyyss") + ".dtsx", pkg.Name), pkg, Nothing)

            pkg.Execute()

            pkg = Nothing

            Dts.TaskResult = Dts.TaskResult

        Catch ex As Exception

            Dts.Events.FireError(0, "Create ssis ClientImport", "The ImportClientComponent Fail " + ex.Message, Nothing, 0)
            Dim App As Microsoft.SqlServer.Dts.Runtime.Application = New Microsoft.SqlServer.Dts.Runtime.Application
            App.SaveToXml(String.Format("C:\Temp\ssis_test_fail_" + Format(Now(), "MMddyyyyss") + ".dtsx", pkg.Name), pkg, Nothing)

            Dts.TaskResult = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

        End Try
    End Sub
    Private Function CreateOLEDBConnection(ByVal p As Microsoft.SqlServer.Dts.Runtime.Package, ByVal ConName As String) As ConnectionManager
        Dim ConMgr As ConnectionManager = p.Connections.Add("OLEDB")
        ConMgr.ConnectionString = Dts.Connections(ConName).ConnectionString '"Data Source=Svrrrdb15;Initial Catalog=RRS_ETL;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;" 'Dts.Variables("User::connection_string_RRS_ETL").Value.ToString 'Dts.Connections("Destination").ConnectionString
        ConMgr.Name = ConName
        ConMgr.Description = "SQL OLE DB Using Destination Connection"
        Return ConMgr
    End Function
    Private Function CreatFileConnection(ByVal p As Microsoft.SqlServer.Dts.Runtime.Package) As ConnectionManager
        Dim conMgr As ConnectionManager = p.Connections.Add("FLATFILE")
        Dim ConName As String = "FlatFileConnection_" + Format(Now(), "MMddyyyy")
        Dim FileName As String = Dts.Variables("User::strTargetFileName").Value.ToString
        Dim TextQualifier As String = Dts.Variables("User::strQualifier").Value.ToString()

        Dim fFile As FileInfo = New FileInfo(Dts.Variables("User::strFullFilePath").Value.ToString())
        Dim fFileName As String = fFile.Name
        fFile = Nothing

        conMgr.ConnectionString = Dts.Variables("User::strFullFilePath").Value.ToString()
        conMgr.Name = ConName
        conMgr.Description = "Flat File Connection "
        conMgr.Properties("Format").SetValue(conMgr, "Delimited")
        conMgr.Properties("ColumnNamesInFirstDataRow").SetValue(conMgr, True)
        conMgr.Properties("HeaderRowsToSkip").SetValue(conMgr, 0)
        'conMgr.Properties("ColumnDelimiter").SetValue(conMgr, vbTab)
        conMgr.Properties("TextQualifier").SetValue(conMgr, TextQualifier)

        conMgr.Properties("Unicode").SetValue(conMgr, False)
        conMgr.Properties("HeaderRowsToSkip").SetValue(conMgr, 0)
        conMgr.Properties("HeaderRowDelimiter").SetValue(conMgr, vbCrLf)

        Return conMgr
    End Function
    Private Function CreateColumnProperties(ByVal FileConn As IDTSConnectionManagerFlatFile100) As IDTSConnectionManagerFlatFileColumn100
        Dim col As IDTSConnectionManagerFlatFileColumn100 = DirectCast(FileConn.Columns.Add(), IDTSConnectionManagerFlatFileColumn100)
        Dim Delimiter As String = Dts.Variables("User::strFileDelimiter").Value.ToString
        Dim TxtQua As String = Dts.Variables("User::strQualifier").Value.ToString()

        col.ColumnType = "Delimited"
        col.TextQualified = True



        Select Case Delimiter.ToUpper()
            Case "vbtab".ToUpper
                col.ColumnDelimiter = vbTab
            Case Else
                col.ColumnDelimiter = Delimiter
        End Select
        'If TxtQua = Nothing Then
        '    col.TextQualified = True
        'Else
        '    col.TextQualified = False
        'End If


        'col.ColumnType = "Delimited"
        'col.ColumnDelimiter = vbTab
        Return col
    End Function
    Private Sub SetDtsColumnProperties(ByVal FlatFileColumn As IDTSConnectionManagerFlatFileColumn100, ByVal reader As SqlDataReader)
        Dim val As Int16
        val = Convert.ToInt16(reader("xtype"))

        'Select Case (Convert.ToInt16(reader("xtype")))
        Select Case (val)
            Case 104 ' DT_BOOL  bit
                FlatFileColumn.DataType = DataType.DT_BOOL
                'RuntimeWrapper.DataType.DT_BOOL;                   
            Case 173 ' DT_BYTES binary, varbinary, timestamp
            Case 165
            Case 189
                FlatFileColumn.DataType = DataType.DT_BYTES
                FlatFileColumn.ColumnWidth = Convert.ToInt32(reader("length"))

            Case 60 ' DT_CY smallmoney, money
            Case 122
                FlatFileColumn.DataType = DataType.DT_CY
                FlatFileColumn.DataPrecision = Convert.ToInt32(reader("prec"))
                FlatFileColumn.DataScale = Convert.ToInt32(reader("scale"))

            Case 61 ' DT_DBTIMESTAMP datetime, smalldatetime
                 FlatFileColumn.DataType = DataType.DT_DBTIMESTAMP
            Case 58
                FlatFileColumn.DataType = DataType.DT_DBTIMESTAMP

            Case 36 ' DT_GUID uniqueidentifier
                FlatFileColumn.DataType = DataType.DT_GUID

            Case 52 ' DT_I2 smallint
                FlatFileColumn.DataType = DataType.DT_I2

            Case 56 ' DT_I4 int
                FlatFileColumn.DataType = DataType.DT_I4

            Case 127 ' DT_I8 bigint
                FlatFileColumn.DataType = DataType.DT_I8

            Case 106 ' DT_NUMERIC decimal, numeric
            Case 108
                FlatFileColumn.DataType = DataType.DT_NUMERIC
                FlatFileColumn.DataPrecision = Convert.ToInt32(reader("prec"))
                FlatFileColumn.DataScale = Convert.ToInt32(reader("scale"))

            Case 59 ' DT_R4 real
                FlatFileColumn.DataType = DataType.DT_R4


            Case 62 ' DT_R8 float
                FlatFileColumn.DataType = DataType.DT_R8


            Case 175 ' DT_STR char, varchar
            Case 167
                FlatFileColumn.DataType = DataType.DT_STR
                FlatFileColumn.ColumnWidth = Convert.ToInt32(reader("length"))


            Case 48 ' DT_UI1 tinyint
                FlatFileColumn.DataType = DataType.DT_UI1


            Case 239 ' DT_WSTR nchar, nvarchar, sql_variant, xml
            Case 231
            Case 98
            Case 241
                FlatFileColumn.DataType = DataType.DT_WSTR
                FlatFileColumn.ColumnWidth = Convert.ToInt32(reader("length"))


            Case 34 ' DT_IMAGE image
                FlatFileColumn.DataType = DataType.DT_IMAGE


            Case 99 ' DT_NTEXT ntext
                FlatFileColumn.DataType = DataType.DT_NTEXT


            Case 35 ' DT_TEXT text
                FlatFileColumn.DataType = DataType.DT_TEXT

        End Select




    End Sub


End Class