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