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

6 comments:

  1. When I try to run this code, I get a "Connection property has not been initialized" error when attempting the first ExecuteNonQuery. I created a DTS Connection and named it "RTDB". When I call "myADONETConnection = DirectCast(TryCast(Dts.Connections("RTDB").AcquireConnection(Dts.Transaction), SqlConnection), SqlConnection)", I get no errors, but a quick check shows that myADONETCOnnection is "Nothing". Any idea why I'm having this problem?

    Thanks!

    ReplyDelete
  2. Aamir, I got your script working by modifying the method of declaring and instantiating the connection. It just wouldn't work when I used your method. I changed it to a more standard approach:

    Dim myADONetConnection As New SqlConnection()
    myADONetConnection.ConnectionString = "Data Source=MyServerName;User ID=MyUserID; Password=MyPassword;Initial Catalog=MyDatabase;"
    myADONetConnection.Open()

    Once I got the connection established, everything else worked like a charm.

    Also, if anyone else is going to try to use the VB version of the code, delete the line that shows:

    Namespace ST_da3127cebb85407989456cc583a3f9e4.csproj

    and the continuation character on the following line. Those don't need to be there, and caused me problems until I removed them.

    -Mark

    ReplyDelete
  3. Hi Mark, thanks for the comments, I created ADO.NET connection and used connection manager in script task. You can initial inside script as you did, It will work both ways.

    Actually code is messed up as i was trying to Edit by some online editor to put in correct format.Will fix and republish it so it will be easy to read.

    Thanks
    Aamir

    ReplyDelete
    Replies
    1. The code does not work...it says Error: The script threw an exception: Incorrect syntax near the keyword 'of'.

      I have fixed all as suggested by mark..please help

      Delete
  4. Hi,

    I'm having trouble getting this to work. I changed the database connection to what Mark suggested but it still won't work.

    I get an error on this line: "myCommand.ExecuteNonQuery()"

    Error is this:
    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
    at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
    at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
    at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
    at ScriptTask_392d6afb58be40b689017fbd0aa27901.ScriptMain.Main() in dts://Scripts/ScriptTask_392d6afb58be40b689017fbd0aa27901/ScriptMain:line 74

    ReplyDelete