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()
sLine = sLine.Replace(ControlChars.Tab, "|")
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))"
Dts.Variables("CREATESQLCMD").Value = sLine
If CBool(Dts.Variables("I").Value) Then
Dts.Variables("CREATESQLCMD").Value = ""
Dts.TaskResult = ScriptResults.SuccessEnd Sub
Happy Learning !!