Posts

Showing posts from June, 2014

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, "|"