Use of Script Task to create SQL table using VB.Net in SSIS

The Below Code will be useful in the cases when we need to deal with flat files, wherein the file structure may be dynamic and we are dealing with multiple files each one with a different schema definition.

The approach will be here is to loop through each file and then read the first line of each file and read the column names from the file and then dynamically create the table and then load the data.

The below code is used to read the column information from the file and then generate a SQL Command which will create the table in the database.

Here it goes - 

Code to dynamically create a table using the Input File Data –

Public Sub Main()
        Dim objReader As New StreamReader(Dts.Variables("SRCDATAFILE").Value.ToString)
        Dim sLine As String = ""
        Dim arrText As New ArrayList()
        sLine = objReader.ReadLine()
        'MsgBox(sLine)
        sLine = sLine.Replace(ControlChars.Tab, "|")
        'MsgBox(sLine)
        sLine = Trim(sLine.Replace(" |", "|").Replace("| ", "|"))
  sLine = "IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE     TABLE_NAME = My_Table') DROP TABLE RBI_Product_stage_tmp; Create Table My_Table ([" & Replace(sLine, "|", "] Varchar(150), [") & "] Varchar(150))"
        'MsgBox(sLine)
        Dts.Variables("CREATESQLCMD").Value = sLine
        If CBool(Dts.Variables("I").Value) Then
            Dts.Variables("CREATESQLCMD").Value = ""
        End If
        objReader.Close()

        Dts.TaskResult = ScriptResults.Success
End Sub

Happy Learning !!

Comments

Popular posts from this blog

SSIS vs. T-SQL

Sync SSAS Cube From One Environment to Another

Pump data from one server to another Efficiently