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

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
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


    Const _SampleRows As Long = 100000
    Const source_directory As String = "C:\Path to your directory"
    Const SchemaName As String = "Schema For Csv Imports On Your Sql Server"
    Const DBConnection As String = "Name Of ADO.Net Connection In Your Package"


    Class DBType


        Private _HasDate As Boolean = False
        Private _HasFloat As Boolean = False
        Private _HasTiny As Boolean = False
        Private _HasSmall As Boolean = False
        Private _HasInt As Boolean = False
        Private _HasBig As Boolean = False
        Private _HasNumber As Boolean = False
        Private _HasString As Boolean = False
        Private _MaxLen As Long = 0
        Private _DBType As String
        Private _Container As String


        Public Property HasDate() As Boolean
            Get
                Return _HasDate
            End Get
            Set(ByVal value As Boolean)
                _HasDate = value
            End Set
        End Property

        Public Property HasFloat() As Boolean
            Get
                Return _HasFloat
            End Get
            Set(ByVal value As Boolean)
                _HasFloat = value
            End Set
        End Property

        Public Property HasTiny() As Boolean
            Get
                Return _HasTiny
            End Get
            Set(ByVal value As Boolean)
                _HasTiny = value
            End Set
        End Property

        Public Property HasSmall() As Boolean
            Get
                Return _HasSmall
            End Get
            Set(ByVal value As Boolean)
                _HasSmall = value
            End Set
        End Property

        Public Property HasInt() As Boolean
            Get
                Return _HasInt
            End Get
            Set(ByVal value As Boolean)
                _HasInt = value
            End Set
        End Property

        Public Property HasBig() As Boolean
            Get
                Return _HasBig
            End Get
            Set(ByVal value As Boolean)
                _HasBig = value
            End Set
        End Property

        Public Property HasString() As Boolean
            Get
                Return _HasString
            End Get
            Set(ByVal value As Boolean)
                _HasString = value
            End Set
        End Property

        Public Property MaxLen() As Long
            Get
                Return _MaxLen
            End Get
            Set(ByVal value As Long)
                _MaxLen = value
            End Set
        End Property

        Public ReadOnly Property DBType() As String
            Get
                Return _DBType
            End Get
        End Property

        Public ReadOnly Property Container() As String
            Get
                Return _Container
            End Get
        End Property


        Public Sub Prepare()
            If _HasString Or (_HasDate And _HasNumber) Then
                _DBType = "NVARCHAR(" & IIf(_MaxLen >= 50, "Max", _MaxLen) & ")"
                _Container = "'"
            ElseIf _HasDate Then
                _DBType = "DATETIME"
                _Container = "'"
            ElseIf _HasFloat Then
                _DBType = "FLOAT"
                _Container = ""
            ElseIf _HasBig Then
                _DBType = "BIGINT"
                _Container = ""
            ElseIf _HasInt Then
                _DBType = "INT"
                _Container = ""
            ElseIf _HasSmall Then
                _DBType = "SMALLINT"
                _Container = ""
            ElseIf _HasTiny Then
                _DBType = "TINYINT"
                _Container = ""
            Else
                _DBType = "NVARCHAR(Max)"
                _Container = "'"
            End If
        End Sub

        Public Sub ComputeValue(ByVal Value As String)
            If IsDate(Value) Then
                _HasDate = True
            ElseIf IsNumeric(Value) And Not IsDate(Value) Then

                _HasNumber = True

                Dim dbl As Double = CDbl(Value)

                If dbl Mod 1 <> 0 Then
                    _HasFloat = True
                ElseIf dbl >= 0 And dbl <= 255 Then
                    _HasTiny = True
                ElseIf dbl >= (0 - 32768) And dbl <= 32767 Then
                    _HasSmall = True
                ElseIf dbl >= (0 - 2147483648) And dbl <= (2147483648 - 1) Then
                    _HasInt = True
                ElseIf dbl >= (0 - 9223372036854775807) And dbl <= (9223372036854775807) Then
                    _HasBig = True
                Else
                    _HasFloat = True
                End If
            Else
                _HasString = True
            End If

            If _MaxLen < Value.Replace("'", "''").Length Then _MaxLen = Value.Replace("'", "''").Length

        End Sub

    End Class






    Public Sub Main()


        
        Dim source_file As String = "*.csv"

        Dim di As New System.IO.DirectoryInfo(source_directory)
        Dim aryFi As IO.FileInfo() = di.GetFiles(source_file)
        Dim fi As IO.FileInfo



        For Each fi In aryFi

            Dim sLine As String
            Dim arrText As New ArrayList()
            Dim objReader As New StreamReader(di.ToString & "\" & fi.ToString)

            Dim DBTypes As New Collection

            Do
                sLine = objReader.ReadLine()
                If Not sLine Is Nothing Then
                    arrText.Add(sLine)
                End If
            Loop Until sLine Is Nothing

            objReader.Close()

            Dim FirstLine As String = ""

            Dim lngSampled As Long = 0

            For Each sLine In arrText
                If FirstLine = "" Then
                    FirstLine = sLine
                ElseIf FirstLine <> "" And _SampleRows >= lngSampled Then
                    Dim ID As Long = 0
                    Dim DTyp As DBType

                    For Each s As String In sLine.Split(",")
                        If DBTypes.Contains("Col_" & ID) = False Then
                            DTyp = New DBType
                            DBTypes.Add(DTyp, "Col_" & ID)
                        Else
                            DTyp = DBTypes("Col_" & ID)
                        End If

                        DTyp.ComputeValue(s)

                        ID += 1

                    Next

                    lngSampled += 1

                End If
            Next


            For Each dt As DBType In DBTypes
                dt.Prepare()
            Next


            Dim FieldNames() As String = Split(FirstLine, ",")
            Dim count As Integer = Split(FirstLine, ",").Length
            Dim mySqlStatement As String = "BEGIN TRY DROP TABLE " & SchemaName & "." & fi.Name.Replace(".", "_") & " END TRY BEGIN CATCH END CATCH CREATE TABLE " & SchemaName & "." & fi.Name.Replace(".", "_") & " ("
            Dim comma As String = ""

            For index As Integer = 0 To count - 1
                mySqlStatement += comma & "[" & FieldNames(index) & "]" & " " & CType(DBTypes("Col_" & index), DBType).DBType
                comma = ","
            Next

            mySqlStatement += ")"


            Dim myADONETConnection As SqlClient.SqlConnection = DirectCast(Dts.Connections(DBConnection).AcquireConnection(Dts.Transaction), SqlClient.SqlConnection)
            Dim myCommand As New SqlClient.SqlCommand(mySqlStatement, myADONETConnection)

            Try
                myCommand.ExecuteNonQuery()
                myADONETConnection.Close()
            Catch ex As Exception
                Dts.TaskResult = ScriptResults.Failure
                Throw ex
                Exit Sub
            End Try


            FirstLine = ""
            Dim line_count As Integer = 1

            mySqlStatement = "INSERT INTO " & SchemaName & "." & fi.Name.Replace(".", "_") & " VALUES "

            Dim comma2 As String = ""
            Dim c As Long = 0

            For Each sLine In arrText


                If Trim(sLine) <> "" Then

                    If FirstLine = "" Then
                        FirstLine = sLine
                    Else
                        line_count += 1

                        Dim fields() As String = Split(sLine, ",")
                        Dim FieldCount As Integer = Split(sLine, ",").Length

                        mySqlStatement &= comma2 & "("

                        comma = ""

                        For FieldIndex As Integer = 0 To FieldCount - 1

                            If FieldIndex <= FieldNames.Length - 1 Then

                                Dim dtyp As DBType

                                dtyp = DBTypes("Col_" & FieldIndex)


                                If Not dtyp.DBType Like "*varchar*" And Trim(fields(FieldIndex)) = "" Then
                                    mySqlStatement += comma & "NULL"
                                Else
                                    If dtyp.DBType = "DATETIME" Then
                                        mySqlStatement += comma & dtyp.Container & CDate(fields(FieldIndex)).ToString("yyyy-MM-dd hh:mm:ss tt") & dtyp.Container
                                    Else
                                        mySqlStatement += comma & dtyp.Container & Replace(fields(FieldIndex), "'", "''") & dtyp.Container
                                    End If
                                End If

                                comma = ","
                            End If
                        Next

                        If FieldCount < FieldNames.Length Then
                            For i = FieldCount To FieldNames.Length - 1
                                mySqlStatement += comma & "NULL"
                            Next
                            comma = ","
                        End If

                        mySqlStatement += ")"

                        c += 1

                        If c = 1000 Then
                            Try
                                myADONETConnection = DirectCast(Dts.Connections(DBConnection).AcquireConnection(Dts.Transaction), SqlClient.SqlConnection)
                                myCommand = New SqlClient.SqlCommand(mySqlStatement, myADONETConnection)
                                myCommand.ExecuteNonQuery()
                                myADONETConnection.Close()
                            Catch ex As Exception
                                Dts.TaskResult = ScriptResults.Failure
                                Throw ex
                                Exit Sub
                            End Try

                            mySqlStatement = "INSERT INTO " & SchemaName & "." & fi.Name.Replace(".", "_") & " VALUES "
                            comma2 = ""
                            c = 0
                        Else
                            comma2 = ","
                        End If




                    End If
                End If
            Next


            If mySqlStatement <> "INSERT INTO " & SchemaName & "." & fi.Name.Replace(".", "_") & " VALUES " Then
                Try
                    myADONETConnection = DirectCast(Dts.Connections(DBConnection).AcquireConnection(Dts.Transaction), SqlClient.SqlConnection)
                    myCommand = New SqlClient.SqlCommand(mySqlStatement, myADONETConnection)
                    myCommand.ExecuteNonQuery()
                    myADONETConnection.Close()
                Catch ex As Exception
                    Dts.TaskResult = ScriptResults.Failure
                End Try


            End If
            Dim file As New System.IO.FileInfo(di.ToString & fi.ToString)

            Try

                file.Delete()

            Catch ex As Exception

                Dts.TaskResult = ScriptResults.Failure

            End Try

            DBTypes = Nothing

        Next



        Dts.TaskResult = ScriptResults.Success
    End Sub

End Class