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