SSIS - [Create table for each flat file and load data]

Senario:
This Code can be used to load multiple files in multiple tables, each file will be loaded into separate table. The table will be created by reading the first line(Hearder row) of flat file and Name of table will be same like flat file.


Solution:
The package reads the file from folder and then create the table on fly as FileName and then inserts data into table. If Table Already exists with the same name it will drop the table and re-Create table

You need to create ADO.Net Connection to the databse where you want to load the files into tables. Drag the script task to the control flow and paste below code.


Here is C# Code

/*
Microsoft SQL Server Integration Services Script Task
Write scripts using Microsoft Visual C# 2008.
The ScriptMain is the entry point class of the script.
*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;


namespace ST_da3127cebb85407989456cc583a3f9e4.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion

/*
The execution engine calls this method when the task executes.
To access the object model, use the Dts property. Connections, variables, events,
and logging features are available as members of the Dts property as shown in the following examples.

To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
To post a log entry, call Dts.Log("This is my log text", 999, null);
To fire an event, do something like the following:
bool bFireAgain = true;
Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, ref bFireAgain);

To use the connections collection use something like the following:
ConnectionManager cm = Dts.Connections.Add("OLEDB");
cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";

Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

To open Help, press F1.
*/

public void Main()
{


SqlConnection myADONETConnection = new SqlConnection();
myADONETConnection = (SqlConnection)(Dts.Connections["ADOAUDIT"].AcquireConnection(Dts.Transaction) as SqlConnection);
MessageBox.Show(myADONETConnection.ConnectionString, "ADOAUDIT");


string line1 = "";
//Reading file names one by one
string SourceDirectory = @"C:\SSISTraining\SourceFiles\DynamicFiles\";
// TODO: Add your code here
string[] fileEntries = Directory.GetFiles(SourceDirectory);
foreach (string fileName in fileEntries)
{
// do something with fileName
MessageBox.Show(fileName);
string columname = "";


//Reading first line of each file and assign to variable
System.IO.StreamReader file2 =
new System.IO.StreamReader(fileName);


string filenameonly=(((fileName.Replace(SourceDirectory,"")).Replace(".txt","")).Replace("\\",""));
line1 = (" IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]."+filenameonly+"') AND type in (N'U'))DROP TABLE [dbo]."+filenameonly+" Create Table dbo."+filenameonly +"("+file2.ReadLine().Replace(","," VARCHAR(100),")+ " VARCHAR(100))").Replace(".txt","");

file2.Close();

MessageBox.Show(line1.ToString());
SqlCommand myCommand = new SqlCommand(line1, myADONETConnection);
myCommand.ExecuteNonQuery();

MessageBox.Show("TABLE IS CREATED");

//Writing Data of File Into Table
int counter = 0;
string line;

System.IO.StreamReader SourceFile =
new System.IO.StreamReader(fileName);
while ((line = SourceFile.ReadLine()) != null)
{

if (counter == 0)
{
columname = line.ToString();
MessageBox.Show("INside IF");
}

else
{
MessageBox.Show("Inside ELSE");
string query = "Insert into dbo." + filenameonly + "(" + columname + ") VALUES('" + line.Replace(",", "','") + "')";
MessageBox.Show(query.ToString());
SqlCommand myCommand1 = new SqlCommand(query, myADONETConnection);
myCommand1.ExecuteNonQuery();
}


counter++;


}

SourceFile.Close();
}
Dts.TaskResult = (int)ScriptResults.Success;
}
}
}

If you like to use VB.Net Code

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


Imports System.Data
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Windows.Forms
Imports System.IO
Imports System.Data.SqlClient


Namespace ST_da3127cebb85407989456cc583a3f9e4.csproj
_
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

#Region "VSTA generated code"
Private Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
#End Region

'
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts property. Connections, variables, events,
' and logging features are available as members of the Dts property as shown in the following examples.
'
' To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
' To post a log entry, call Dts.Log("This is my log text", 999, null);
' To fire an event, do something like the following:
' bool bFireAgain = true;
' Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, ref bFireAgain);
'
' To use the connections collection use something like the following:
' ConnectionManager cm = Dts.Connections.Add("OLEDB");
' cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
'
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Help, press F1.
'


Public Sub Main()


Dim myADONETConnection As New SqlConnection()
myADONETConnection = DirectCast(TryCast(Dts.Connections("ADOAUDIT").AcquireConnection(Dts.Transaction), SqlConnection), SqlConnection)
MessageBox.Show(myADONETConnection.ConnectionString, "AD.AUDIT")


Dim line1 As String = ""
'Reading file names one by one
Dim SourceDirectory As String = "C:\SSISTraining\SourceFiles\DynamicFiles\"
' TODO: Add your code here
Dim fileEntries As String() = Directory.GetFiles(SourceDirectory)
For Each fileName As String In fileEntries
' do something with fileName
MessageBox.Show(fileName)
Dim columname As String = ""


'Reading first line of each file and assign to variable
Dim file2 As New System.IO.StreamReader(fileName)

Dim filenameonly As String = (((fileName.Replace(SourceDirectory, "")).Replace(".txt", "")).Replace("\", ""))
line1 = (" IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo]." & filenameonly & "') AND type in (N'U'))DROP TABLE [dbo]." & filenameonly & " Create Table dbo." & filenameonly & "(" & file2.ReadLine().Replace(",", " VARCHAR(100),") & " VARCHAR(100))").Replace(".txt", "")

file2.Close()

MessageBox.Show(line1.ToString())
Dim myCommand As New SqlCommand(line1, myADONETConnection)
myCommand.ExecuteNonQuery()

MessageBox.Show("TABLE IS CREATED")

'Writing Data of File Into Table
Dim counter As Integer = 0
Dim line As String = ""

Dim SourceFile As New System.IO.StreamReader(fileName)
While (InlineAssignHelper(line, SourceFile.ReadLine())) IsNot Nothing

If counter = 0 Then
columname = line.ToString()
MessageBox.Show("INside IF")
Else

MessageBox.Show("Inside ELSE")
Dim query As String = "Insert into dbo." & filenameonly & "(" & columname & ") VALUES('" & line.Replace(",", "','") & "')"
MessageBox.Show(query.ToString())
Dim myCommand1 As New SqlCommand(query, myADONETConnection)
myCommand1.ExecuteNonQuery()
End If




counter += 1
End While

SourceFile.Close()
Next
Dts.TaskResult = CInt(ScriptResults.Success)
End Sub
Private Shared Function InlineAssignHelper(Of T)(ByRef target As T, ByVal value As T) As T
target = value
Return value
End Function
End Class
End Namespace

In above code I renamed ADO.NET Connection to ADOAUDIT that you can change to anything you like.

MessageBoxes are left for debugging, Please remove them after debugging.

Thanks