SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Stan Kulp / Dynamically create and populate staging tables from CSV files / Latest Posts

RE: Dynamically create and populate staging tables from CSV files

Lidou123 — Fri, 11 Jan 2013 13:37:28 GMT

Hi Stan,Thank you for your answer.Ok, I will try your script with csv file.

RE: Dynamically create and populate staging tables from CSV files

Stan Kulp-439977 — Thu, 10 Jan 2013 15:26:37 GMT

I am not sure of what you are asking, but an Excel file with 1000 lines is not a big file. Exporting it to CSV would be trivial.Do you know how to export a CSV file from Excel by doing a "File-Save As" then choosing "MS-DOS CSV" as the type?

RE: Dynamically create and populate staging tables from CSV files

Lidou123 — Thu, 10 Jan 2013 14:56:37 GMT

Hi Stan,Thank U for your answer.Do U think that there is a solution with excel file source and thousand lines in a EXCEL file ??

RE: Dynamically create and populate staging tables from CSV files

Lidou123 — Thu, 10 Jan 2013 14:45:41 GMT

Please !!Do U know if there is another solution to do this with thousand lines in a file ?

RE: Dynamically create and populate staging tables from CSV files

Stan Kulp-439977 — Thu, 10 Jan 2013 14:44:59 GMT

Reading Excel files is not trivial. You can export them to CSV files out of Excel. CSV files will work fine.You would have to convert them manually one-at-a-time, unless you purchase a third-party application like the one at this link:http://www.softinterface.com/Convert-XLS/Features/Convert-XLSX-To-CSV.htm

RE: Dynamically create and populate staging tables from CSV files

Lidou123 — Thu, 10 Jan 2013 13:40:44 GMT

Hi All,Can I use this with excel files ??

RE: Dynamically create and populate staging tables from CSV files

bishnu.agrawal — Sat, 23 Jun 2012 10:54:04 GMT

Can Please any1 tell me what if we really want to know the data types of the columns and create the table accordingly. For small no. of files we can even do this thing 1 by 1 . But if the files are in 1000 s then what should be the proper solution to built the staging database. Please suggest some 1 its urgently required. Can I use any .dll file and modify it accodingly to get the requirement done.

RE: Dynamically create and populate staging tables from CSV files

SJTerrill — Thu, 12 Jan 2012 11:38:04 GMT

[quote]Although I don't particularly care for SSIS nor the fact that the technique in this article had to use a script to do the job (I did my thing 100% in T-SQL with no problems), the article is a reasonable introduction as to how you might begin to approach such a thing in SSIS.[/quote]Jeff,I for one would be interested in getting eyes on the T-SQL solution you mention!SJT

RE: Dynamically create and populate staging tables from CSV files

Jeff Moden — Thu, 12 Jan 2012 09:25:22 GMT

[quote][b]patrickmcginnis59 (1/10/2012)[/b][hr][quote][b]Jeff Moden (1/10/2012)[/b]Of course, you could also pre-process tables before they come anywhere near SQL Server but I've found that writing such code is a bit of a fright because you might not have set based, declarative tools such as are available in SQL Server to keep from having to write read/write routines with lots of loops and decisions on your own.[/quote]Actually, performing loops and tests in other languages is much easier. T-SQL is not that great a language in regards to either performance or feature sets. T-SQL's main advantage is that it has a database system hooked up to it.I don't think you should give up on loops and tests just because T-SQL is not that great a programming environment, unless you're really just not that interested in programming in general./opinion[/quote]I'll have to resort to the "It Depends" aspect. If you want to check all data in a "field", it's a whole lot easier to do a test for it in a column in T-SQL than it is to write code to cycle through every row of that "field". Because T-SQL will use the very low-level "Pseudo Cursors" behind the scenes, it'll do it all at the same or almost the same speeds as writing an external validation script.

RE: Dynamically create and populate staging tables from CSV files

Jeff Moden — Thu, 12 Jan 2012 09:21:34 GMT

[quote][b]surreydude. (1/11/2012)[/b][hr][quote][b]Jeff Moden (1/10/2012)[/b][hr]Stan,Nice step-by-step article with some timely graphics to show how to do things. Although it wasn't specifically named as such, I believe that folks will come to realize that this is a very good "spackle" article that demonstrates the beginnings of a technique. Like any "spackle" article, it's not meant to be a complete solution but it'll sure get some thought juices going for people who have ever had to import "unknown number of columns" files like I've had to do in the past. You could have saved yourself a bit of commentary by identifying that the popups where there only for verification during development and, perhaps, more clearly identifying not only when this technique might be used along with suggested "next steps", but it's a good article. Thanks for taking the time to write it and share it.[/quote]Sorry Jeff - couldn't help laughing at this post. Not sure if you are aware of the slang "spackle": [url=http://www.google.co.uk/url?sa=t&rct=j&q=psackle&source=web&cd=2&ved=0CEEQFjAB&url=http%3A%2F%2Fwww.urbandictionary.com%2Fdefine.php%3Fterm%3Dspackle&ei=f0cNT4nkI4m2hAepkeGSAg&usg=AFQjCNEcmnKK2GmZtiQuu8j8vGRiLbGs-Q&cad=rja]urban dictionary[/url]:hehe:Poor Stan[/quote]Now THAT's funny. :-D But, no... not the type of spackle I was referring to.

RE: Dynamically create and populate staging tables from CSV files

mishaluba — Thu, 12 Jan 2012 08:57:25 GMT

Thank you for the article. For .NET-challenged SQL developers like myself this provides a great starting point to go on and adapt this approach for our specific needs. Also thank you to everybody who contributed subsequent enhancements. You are a great bunch!

RE: Dynamically create and populate staging tables from CSV files

minehere — Thu, 12 Jan 2012 07:21:22 GMT

This is good to generate an initial cut of the SQL code that I will then use at the start of my SSIS package to create the staging tables if not present, Of course for that I do not need the data loading part. I would use a dataflow task to do the actual loading.Saves me some typing and hence typos.

RE: Dynamically create and populate staging tables from CSV files

surreydude. — Wed, 11 Jan 2012 01:28:53 GMT

[quote][b]Jeff Moden (1/10/2012)[/b][hr]Stan,Nice step-by-step article with some timely graphics to show how to do things. Although it wasn't specifically named as such, I believe that folks will come to realize that this is a very good "spackle" article that demonstrates the beginnings of a technique. Like any "spackle" article, it's not meant to be a complete solution but it'll sure get some thought juices going for people who have ever had to import "unknown number of columns" files like I've had to do in the past. You could have saved yourself a bit of commentary by identifying that the popups where there only for verification during development and, perhaps, more clearly identifying not only when this technique might be used along with suggested "next steps", but it's a good article. Thanks for taking the time to write it and share it.[/quote]Sorry Jeff - couldn't help laughing at this post. Not sure if you are aware of the slang "spackle": [url=http://www.google.co.uk/url?sa=t&rct=j&q=psackle&source=web&cd=2&ved=0CEEQFjAB&url=http%3A%2F%2Fwww.urbandictionary.com%2Fdefine.php%3Fterm%3Dspackle&ei=f0cNT4nkI4m2hAepkeGSAg&usg=AFQjCNEcmnKK2GmZtiQuu8j8vGRiLbGs-Q&cad=rja]urban dictionary[/url]:hehe:Poor Stan

RE: Dynamically create and populate staging tables from CSV files

tobe_ha — Wed, 11 Jan 2012 01:21:59 GMT

[quote]Actually, performing loops and tests in other languages is much easier. T-SQL is not that great a language in regards to either performance or feature sets. T-SQL's main advantage is that it has a database system hooked up to it.I don't think you should give up on loops and tests just because T-SQL is not that great a programming environment, unless you're really just not that interested in programming in general./opinion[/quote]No TSQL is not the best language for performing loops. But there are few scenarios in etl, where loops are the best algorithm to process data. The transformations after staging fore me are typical exercises of set-based processing.

RE: Dynamically create and populate staging tables from CSV files

patrickmcginnis59 — Tue, 10 Jan 2012 09:30:04 GMT

[quote][b]Jeff Moden (1/10/2012)[/b]Of course, you could also pre-process tables before they come anywhere near SQL Server but I've found that writing such code is a bit of a fright because you might not have set based, declarative tools such as are available in SQL Server to keep from having to write read/write routines with lots of loops and decisions on your own.[/quote]Actually, performing loops and tests in other languages is much easier. T-SQL is not that great a language in regards to either performance or feature sets. T-SQL's main advantage is that it has a database system hooked up to it.I don't think you should give up on loops and tests just because T-SQL is not that great a programming environment, unless you're really just not that interested in programming in general./opinion

RE: Dynamically create and populate staging tables from CSV files

Stan Kulp-439977 — Tue, 10 Jan 2012 08:57:05 GMT

@bhavjayTo me, a staging table is just that, the first stage of processing the data. Since we don't really know what kind of data the fields are going to hold, we poke the data into VARCHARs just to get it into a table. The next stage would be actually looking at the data and writing an insert into/select statement that would cast the fields to the desired types and insert the records into the destination table.

RE: Dynamically create and populate staging tables from CSV files

Stan Kulp-439977 — Tue, 10 Jan 2012 08:44:25 GMT

That's some really nice code. I just wrote mine to show that the idea would work. Yours would work well.Thanks!

RE: Dynamically create and populate staging tables from CSV files

Mike Dougherty-384281 — Tue, 10 Jan 2012 08:17:56 GMT

[quote][b]jimbobmcgee (1/10/2012)[/b][hr]That said, I do want to offer some pointers with the .NET code itself, which I feel would make this better as a teaching article or reusable code block:[enumerated list 1-14 of constructive feedback][/quote]Thank you for the additional insights. If the original is a solution-starter, your comments are a reminder to keep a quality end in mind during development. Since we rarely go back to improve these kinds of utilities, it's a good idea to get them as near-optimal as possible on the first effort. (time permitting)

RE: Dynamically create and populate staging tables from CSV files

eephus101 — Tue, 10 Jan 2012 08:13:49 GMT

Hi Stan, I very much like the article.Sometimes I just want to ram some data into a database simply to explore the data. This technique can be very helpful when that is my aim.Nice work.

RE: Dynamically create and populate staging tables from CSV files

ahperez — Tue, 10 Jan 2012 08:09:49 GMT

Thanks Stan for taking the time to write the article and post the script. I often have to import files from wide-ranging sources with little information regarding the specific contents of these files. You've given me lots of food for thought in how to do this more efficiently. I'll definitely experiment with the code and technique you've shown here.

RE: Dynamically create and populate staging tables from CSV files

Stan Kulp-439977 — Tue, 10 Jan 2012 06:29:25 GMT

The only reason I wrote this is because a responder to one of my previous articles asked how to do it. I don't consider it to be something all that useful for all the reasons you stated. I think it is cool how fast it creates and populates the tables. The code works. As a developer, I just enjoy making something work sometimes, even if it is silly.

RE: Dynamically create and populate staging tables from CSV files

Jeff Moden — Tue, 10 Jan 2012 06:27:06 GMT

Stan,Nice step-by-step article with some timely graphics to show how to do things. Although it wasn't specifically named as such, I believe that folks will come to realize that this is a very good "spackle" article that demonstrates the beginnings of a technique. Like any "spackle" article, it's not meant to be a complete solution but it'll sure get some thought juices going for people who have ever had to import "unknown number of columns" files like I've had to do in the past. You could have saved yourself a bit of commentary by identifying that the popups where there only for verification during development and, perhaps, more clearly identifying not only when this technique might be used along with suggested "next steps", but it's a good article. Thanks for taking the time to write it and share it.

RE: Dynamically create and populate staging tables from CSV files

Jeff Moden — Tue, 10 Jan 2012 06:16:19 GMT

[quote][b]surreydude. (1/10/2012)[/b][hr]A great little technique but I would probably only use this kind of thing for an initial Proof of Concept. Rarely would you want to (or be allowed to) create dynamic table structures with possibly inaccurate data types and columns sizes in a production database. Morever, I suspect the ETL routines would fail if you have changing data types. Consider that the CSV has nulls in the first row on the first load - how would you know what the data type is then? What happens if the data is longer than 20 characters?[/quote]I agree... it's a rare thing. However, I have worked with vendors such as "double-click.net" that will only provide the data in a CSV format as if it were exported from a reporting system or a spreadsheet where there are a handful of key columns on the left and an unknown quantity of columns on the right. The unknown columns actually needed to be paired in the final import. Each column was marked with a name which included the type of column it was and pairs of like-named columns could be paired based on those names. In our case, the files could be anywhere from 11 columns (9 key columns) to hundreds of columns wide based on how many active internet ads a customer may have for any given week.Although I don't particularly care for SSIS nor the fact that the technique in this article had to use a script to do the job (I did my thing 100% in T-SQL with no problems), the article is a reasonable introduction as to how you might begin to approach such a thing in SSIS. The author included verification popups for experimentation purposes and once such a system is in place, these popups can be easily removed for hands-off scheduled imports.The "final move" to real tables would be done using a dynamic unpivot (you already have the column names in a separate table to make this a bit easier) and then dynamically/conditionally repivoted to meet the structure of the final tables for insert/update. But before you can do all of that, you have to get the data in to work on it and this article shows one possible method for doing such a thing.Of course, you could also pre-process tables before they come anywhere near SQL Server but I've found that writing such code is a bit of a fright because you might not have set based, declarative tools such as are available in SQL Server to keep from having to write read/write routines with lots of loops and decisions on your own.

RE: Dynamically create and populate staging tables from CSV files

fraser.graham — Tue, 10 Jan 2012 06:13:59 GMT

Hi,Thanks Stan for submitting this. I regularly encounter csv documents that do not have a set number of columns or where the column names are changed regularly. This solution is ideal for my purposes. I can then change the column names more easily using T-SQL.Thanks :-)I have enhanced this script to do the following:-Determine data typesSkip blank rows on the csvInsert null for missing columns on any csv rowInsert 1000 rows at a timeI'm sure that there will be some bugs in this script and I will probably find them in the coming months. Please treat this as a beta version if you use it.You must first amend the constant variables at the top of this code to use it.[code="vb"]' Microsoft SQL Server Integration Services Script Task' Write scripts using Microsoft Visual Basic 2008.' The ScriptMain is the entry point class of the script.Imports SystemImports System.DataImports System.MathImports Microsoft.SqlServer.Dts.RuntimeImports System.IO<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _<System.CLSCompliantAttribute(False)> _Partial Public Class ScriptMain Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase Enum ScriptResults Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure End Enum Const _SampleRows As Long = 100000 Const source_directory As String = "C:\Path to your directory" Const SchemaName As String = "Schema For Csv Imports On Your Sql Server" Const DBConnection As String = "Name Of ADO.Net Connection In Your Package" Class DBType Private _HasDate As Boolean = False Private _HasFloat As Boolean = False Private _HasTiny As Boolean = False Private _HasSmall As Boolean = False Private _HasInt As Boolean = False Private _HasBig As Boolean = False Private _HasNumber As Boolean = False Private _HasString As Boolean = False Private _MaxLen As Long = 0 Private _DBType As String Private _Container As String Public Property HasDate() As Boolean Get Return _HasDate End Get Set(ByVal value As Boolean) _HasDate = value End Set End Property Public Property HasFloat() As Boolean Get Return _HasFloat End Get Set(ByVal value As Boolean) _HasFloat = value End Set End Property Public Property HasTiny() As Boolean Get Return _HasTiny End Get Set(ByVal value As Boolean) _HasTiny = value End Set End Property Public Property HasSmall() As Boolean Get Return _HasSmall End Get Set(ByVal value As Boolean) _HasSmall = value End Set End Property Public Property HasInt() As Boolean Get Return _HasInt End Get Set(ByVal value As Boolean) _HasInt = value End Set End Property Public Property HasBig() As Boolean Get Return _HasBig End Get Set(ByVal value As Boolean) _HasBig = value End Set End Property Public Property HasString() As Boolean Get Return _HasString End Get Set(ByVal value As Boolean) _HasString = value End Set End Property Public Property MaxLen() As Long Get Return _MaxLen End Get Set(ByVal value As Long) _MaxLen = value End Set End Property Public ReadOnly Property DBType() As String Get Return _DBType End Get End Property Public ReadOnly Property Container() As String Get Return _Container End Get End Property Public Sub Prepare() If _HasString Or (_HasDate And _HasNumber) Then _DBType = "NVARCHAR(" & IIf(_MaxLen >= 50, "Max", _MaxLen) & ")" _Container = "'" ElseIf _HasDate Then _DBType = "DATETIME" _Container = "'" ElseIf _HasFloat Then _DBType = "FLOAT" _Container = "" ElseIf _HasBig Then _DBType = "BIGINT" _Container = "" ElseIf _HasInt Then _DBType = "INT" _Container = "" ElseIf _HasSmall Then _DBType = "SMALLINT" _Container = "" ElseIf _HasTiny Then _DBType = "TINYINT" _Container = "" Else _DBType = "NVARCHAR(Max)" _Container = "'" End If End Sub Public Sub ComputeValue(ByVal Value As String) If IsDate(Value) Then _HasDate = True ElseIf IsNumeric(Value) And Not IsDate(Value) Then _HasNumber = True Dim dbl As Double = CDbl(Value) If dbl Mod 1 <> 0 Then _HasFloat = True ElseIf dbl >= 0 And dbl <= 255 Then _HasTiny = True ElseIf dbl >= (0 - 32768) And dbl <= 32767 Then _HasSmall = True ElseIf dbl >= (0 - 2147483648) And dbl <= (2147483648 - 1) Then _HasInt = True ElseIf dbl >= (0 - 9223372036854775807) And dbl <= (9223372036854775807) Then _HasBig = True Else _HasFloat = True End If Else _HasString = True End If If _MaxLen < Value.Replace("'", "''").Length Then _MaxLen = Value.Replace("'", "''").Length End Sub End Class Public Sub Main() Dim source_file As String = "*.csv" Dim di As New System.IO.DirectoryInfo(source_directory) Dim aryFi As IO.FileInfo() = di.GetFiles(source_file) Dim fi As IO.FileInfo For Each fi In aryFi Dim sLine As String Dim arrText As New ArrayList() Dim objReader As New StreamReader(di.ToString & "\" & fi.ToString) Dim DBTypes As New Collection Do sLine = objReader.ReadLine() If Not sLine Is Nothing Then arrText.Add(sLine) End If Loop Until sLine Is Nothing objReader.Close() Dim FirstLine As String = "" Dim lngSampled As Long = 0 For Each sLine In arrText If FirstLine = "" Then FirstLine = sLine ElseIf FirstLine <> "" And _SampleRows >= lngSampled Then Dim ID As Long = 0 Dim DTyp As DBType For Each s As String In sLine.Split(",") If DBTypes.Contains("Col_" & ID) = False Then DTyp = New DBType DBTypes.Add(DTyp, "Col_" & ID) Else DTyp = DBTypes("Col_" & ID) End If DTyp.ComputeValue(s) ID += 1 Next lngSampled += 1 End If Next For Each dt As DBType In DBTypes dt.Prepare() Next Dim FieldNames() As String = Split(FirstLine, ",") Dim count As Integer = Split(FirstLine, ",").Length Dim mySqlStatement As String = "BEGIN TRY DROP TABLE " & SchemaName & "." & fi.Name.Replace(".", "_") & " END TRY BEGIN CATCH END CATCH CREATE TABLE " & SchemaName & "." & fi.Name.Replace(".", "_") & " (" Dim comma As String = "" For index As Integer = 0 To count - 1 mySqlStatement += comma & "[" & FieldNames(index) & "]" & " " & CType(DBTypes("Col_" & index), DBType).DBType comma = "," Next mySqlStatement += ")" Dim myADONETConnection As SqlClient.SqlConnection = DirectCast(Dts.Connections(DBConnection).AcquireConnection(Dts.Transaction), SqlClient.SqlConnection) Dim myCommand As New SqlClient.SqlCommand(mySqlStatement, myADONETConnection) Try myCommand.ExecuteNonQuery() myADONETConnection.Close() Catch ex As Exception Dts.TaskResult = ScriptResults.Failure Throw ex Exit Sub End Try FirstLine = "" Dim line_count As Integer = 1 mySqlStatement = "INSERT INTO " & SchemaName & "." & fi.Name.Replace(".", "_") & " VALUES " Dim comma2 As String = "" Dim c As Long = 0 For Each sLine In arrText If Trim(sLine) <> "" Then If FirstLine = "" Then FirstLine = sLine Else line_count += 1 Dim fields() As String = Split(sLine, ",") Dim FieldCount As Integer = Split(sLine, ",").Length mySqlStatement &= comma2 & "(" comma = "" For FieldIndex As Integer = 0 To FieldCount - 1 If FieldIndex <= FieldNames.Length - 1 Then Dim dtyp As DBType dtyp = DBTypes("Col_" & FieldIndex) If Not dtyp.DBType Like "*varchar*" And Trim(fields(FieldIndex)) = "" Then mySqlStatement += comma & "NULL" Else If dtyp.DBType = "DATETIME" Then mySqlStatement += comma & dtyp.Container & CDate(fields(FieldIndex)).ToString("yyyy-MM-dd hh:mm:ss tt") & dtyp.Container Else mySqlStatement += comma & dtyp.Container & Replace(fields(FieldIndex), "'", "''") & dtyp.Container End If End If comma = "," End If Next If FieldCount < FieldNames.Length Then For i = FieldCount To FieldNames.Length - 1 mySqlStatement += comma & "NULL" Next comma = "," End If mySqlStatement += ")" c += 1 If c = 1000 Then Try myADONETConnection = DirectCast(Dts.Connections(DBConnection).AcquireConnection(Dts.Transaction), SqlClient.SqlConnection) myCommand = New SqlClient.SqlCommand(mySqlStatement, myADONETConnection) myCommand.ExecuteNonQuery() myADONETConnection.Close() Catch ex As Exception Dts.TaskResult = ScriptResults.Failure Throw ex Exit Sub End Try mySqlStatement = "INSERT INTO " & SchemaName & "." & fi.Name.Replace(".", "_") & " VALUES " comma2 = "" c = 0 Else comma2 = "," End If End If End If Next If mySqlStatement <> "INSERT INTO " & SchemaName & "." & fi.Name.Replace(".", "_") & " VALUES " Then Try myADONETConnection = DirectCast(Dts.Connections(DBConnection).AcquireConnection(Dts.Transaction), SqlClient.SqlConnection) myCommand = New SqlClient.SqlCommand(mySqlStatement, myADONETConnection) myCommand.ExecuteNonQuery() myADONETConnection.Close() Catch ex As Exception Dts.TaskResult = ScriptResults.Failure End Try End If Dim file As New System.IO.FileInfo(di.ToString & fi.ToString) Try file.Delete() Catch ex As Exception Dts.TaskResult = ScriptResults.Failure End Try DBTypes = Nothing Next Dts.TaskResult = ScriptResults.Success End SubEnd Class[/code]

RE: Dynamically create and populate staging tables from CSV files

Jeff Moden — Tue, 10 Jan 2012 05:55:20 GMT

[quote][b]craig 81366 (1/10/2012)[/b][hr]The trouble then is that validation will only be performed when getting data from the staging tables to the destination tables.I consider SQL, as a set based language to be relatively poorly suited to the task of identifying & excluding specific problematic rows.Have I understood correctly?How would you handle validation moving/copying data to the destination tables?[/quote]Gosh Craig, the only reason why I use staging tables to begin with is to do the very things that you've stated that SQL has a problem with (and it doesn't). I can easily validate data types, ranges and domains of data, do checks against known/expected values in lookup table, etc, ad infinitum, and all before I let possibly bad data anywhere near my real tables. Each row is premarked for insert, update, or error (along with the type of error) and the "move" code consists of two inserts (one for the good rows, one for the error rows to an errata table) and an update.The "final move" to real tables would be done using a dynamic unpivot (you already have the column names in a separate table to make this a bit easier) and then dynamically/conditionally repivoted to meet the structure of the final tables for insert/update. But before you can do all of that, you have to get the data in to work on it and this article shows one possible method for doing such a thing.

RE: Dynamically create and populate staging tables from CSV files

CGSJohnson — Tue, 10 Jan 2012 05:45:13 GMT

Thanks for posting the article.I think that everyone is mis-understanding. It is not a fully-flushed out implementation, but, as the author mentioned, only to be used for a proof-of-concept, for small data loads.Thanks...Chris

RE: Dynamically create and populate staging tables from CSV files

jimbobmcgee — Tue, 10 Jan 2012 04:47:17 GMT

Something inside me really wanted to hate this technique, but I know it has its uses in a pure "staging" environment (i.e. one where you have already committed to do the T/L part of ETL in procedures between the stage and the ODS).That said, I do want to offer some pointers with the .NET code itself, which I feel would make this better as a teaching article or reusable code block:1. Remove all MessageBox.Show calls. Replace with Dts.Log(message) -- might be obvious but noone wants to press OK 30,000 times to load a file2. In any case, favour StringBuilder over message += "blah"3. Don't populate an ArrayList with the lines, they have no requirement to stay in memory once they have been read/inserted -- instead, do your processing in the Do/While Not (sLine Is Nothing) loop and discard each row once it is created. You can use an isFirst Boolean, or a lineCount Integer to handle your first-line semantics inside that loop -- this should allow for files far larger than 10,000 rows4. Consider System.IO.Path.Combine over (di.ToString & fi.ToString) when building paths programatically4a. For what it is worth, fi.FullName is the full path to the file (so the di.ToString & fi.ToString combo is not needed)5. Consider System.IO.Path.GetFileNameWithoutExtension(fi.Name) over fi.ToString.Substring(0, fi.ToString.Length - 4)6. Wrap your StreamReader code in a Using/End Using block [i.e. Using objReader As StreamReader = New StreamReader(...)] -- if the ReadLine code throws an exception (e.g. if a line length is > 2bn), the End Using will automatically clean up7. Consider batching your inserts, running 10-100 at a time. Build the insert statements up in a StringBuilder, separating with a semi-colon/newline combination [builder.Append(sql).AppendLine(";"c)] and run them when ((lineCount - 1) Mod batchSize = 0)7a. Consider offering the batchSize as a Dts.Variable8. Properly escape table/column names, either with a RegEx replacement for any non-alphanumeric character, or by using a SqlClient.SqlCommandBuilder object, with QuotePrefix = "[", QuoteSuffix = "]", using the QuoteIdentifier method.9. Properly escape CSV data values, with a SqlClient.SqlCommandBuilder object, with QuotePrefix = "'", QuoteSuffix = "'", using the QuoteIdentifier method.9a. Alternatively, consider building your insert string as INSERT INTO table VALUES (@p1, @p2, @p3, ...); and using the Parameters collection on the SqlCommand object to populate the values10. Consider NVARCHAR(MAX) instead of VARCHAR(20) as your default data type -- .NET strings are UTF16/UCS2 as standard, so it makes sense to store them as such, and the MAX length prevents data truncation11. Catch specific exceptions, if you are going to handle them, e.g. IO.IOException is thrown by FileInfo.MoveTo if a file already exists12a. Consider checking that the backup folder exists before attempting to copy to it [If Not Directory.Exists(Path.Combine(di.FullName, "Backup")) Then Directory.CreateDirectory(Path.Combine(di.FullName, "Backup"))]12b. Consider checking that the backup file does not exist before attempting to move the source file [If Not File.Exists(Path.Combine(Path.Combine(di.FullName, "Backup"), fi.Name)) Then File.Delete(Path.Combine(Path.Combine(di.FullName, "Backup"), fi.Name))] 12c. Consider storing the backup folder location in a variable to reduce on Path.Combine calls -- allow the user to configure this location with a Dts.Variable13. Consider making a IDENTITY PK on your imported tables, to aid in row identification later in the T/L process, either named tableName_id or according to a Dts.Variable -- you could turn this feature on or off by whether the variable is set or not (String.IsNullOrEmpty is your friend here).14. Consider a recreateSchema Dts.Variable which triggers the drops of existing tables -- this will allow further development of the staging database, e.g. indexing, partitioning, which would be retained between data loads

RE: Dynamically create and populate staging tables from CSV files

tobe_ha — Tue, 10 Jan 2012 03:55:30 GMT

Regarding the datatype discussion:I think it's a great idea to copy data to staging tables using a varchar datatype in the first step. You can check the datatypes in the next step with sql (sql is not poor ;-)) or in a dataflow. The important thing is, that you have a place within the database where you have all source data (also the bad one) decoupled from its source system(s).In most circumstances this makes the subsequent operations easier (statistics on bad data, determination of deltas, restarting after an abortion, et cetera)

RE: Dynamically create and populate staging tables from CSV files

crazy4sql — Tue, 10 Jan 2012 02:35:46 GMT

i feel it as too much complicated when we have the import/export functionality available.

RE: Dynamically create and populate staging tables from CSV files

surreydude. — Tue, 10 Jan 2012 01:06:12 GMT

A great little technique but I would probably only use this kind of thing for an initial Proof of Concept. Rarely would you want to (or be allowed to) create dynamic table structures with possibly inaccurate data types and columns sizes in a production database. Morever, I suspect the ETL routines would fail if you have changing data types. Consider that the CSV has nulls in the first row on the first load - how would you know what the data type is then? What happens if the data is longer than 20 characters?

RE: Dynamically create and populate staging tables from CSV files

craig 81366 — Tue, 10 Jan 2012 01:00:16 GMT

I have a concern with this approach.Since the objective is to "get data from a file into a (staging) table with as little fuss as possible", I presume you want little or no validation during this step.The trouble then is that validation will only be performed when getting data from the staging tables to the destination tables.I consider SQL, as a set based language to be relatively poorly suited to the task of identifying & excluding specific problematic rows.Have I understood correctly?How would you handle validation moving/copying data to the destination tables?

RE: Dynamically create and populate staging tables from CSV files

Rogman — Mon, 09 Jan 2012 23:27:01 GMT

The code creates all the fields as varchar(20), though you could do an IsNumeric() check and then create the column as an int data type instead.

RE: Dynamically create and populate staging tables from CSV files

bhavjay — Mon, 09 Jan 2012 22:31:11 GMT

What will be the data type of columns? Will it take the appropriate types such as for integer field int etc...Thanksbhav

Dynamically create and populate staging tables from CSV files

Stan Kulp-439977 — Mon, 09 Jan 2012 22:10:51 GMT

Comments posted to this topic are about the item [B]Dynamically create and populate staging tables from CSV files[/B]