Junk Drawer

• Download jd.x64.zip - 1 MB
• Download jd.x86.zip - 1 MB
• Download source - 2 MB

• Install SQL Server Management Studio.
• Find Tasks and choose Import Data.
• Select "Flat File Source."
• Browse for the file.
• Preview the data.
• Specify the delimiter.
• Specify if the first row is column names.
• Preview the data (again).
• Go to each column and choose the correct data type or use "Suggest Types" feature.
• Choose if you want to save the SSIS package for later.
• Execute it

Requirements

A Demo

 Collapse | Copy Code

<transformalize>
    <processes>
        <add name="JunkDrawer">
            <connections>
                <add name="output" connection-string="connection-string" />
            </connections>
        </add>
    </processes>
</transformalize> 

 Collapse | Copy Code

Name,Birthday,Points
Dale,3/3/1981 9 AM,73
Tara,12/31/1990,1042
Grace,9/9/2000 11 PM,56
Gavin,7/3/2010,13        

 Collapse | Copy Code

jd c:\sample.txt 

 Collapse | Copy Code

SELECT Name, Birthday, Points FROM sample;  
Name  Birthday                Points
----- ----------------------- -----------
Dale  1981-03-03 09:00:00.000 73
Gavin 2010-07-03 00:00:00.000 13
Tara  1990-12-31 00:00:00.000 1042
Grace 2000-09-09 23:00:00.000 56    

 Collapse | Copy Code

CREATE TABLE sample(
    BirthDay DATETIME,
    Name NVARCHAR(5),
    Points INT
);    

How Does it Work?

1. the delimiter
2. the column names (if available)
3. the column data types

Delimiters

Column Names

• Are there any duplicate field values?
• Are there any empty values?
• Are there any white space values?
• Are there any numeric values?
• Are there any date time values?

Data Types

Some Code?

 Collapse | Copy Code

public class Program {

    static void Main(string[] args) {

        GlobalDiagnosticsContext.Set("process", Common.LogLength("JunkDrawer", 16));
        GlobalDiagnosticsContext.Set("entity", Common.LogLength("Request"));

        var request = new Request(args);

        if (!request.IsValid) {
            LogManager.GetLogger(string.Empty).Error(request.Message);
            Environment.Exit(1);
        }

        new FileImporter().Import(request.FileInfo);
    }
}

1. I'm setting some global variables for NLog.
2. I'm validating the Request:
   1. Did the user pass in a file name?
   2. Is the file there?
   3. Is it in use? I give up after 5 tries.
3. I'm importing the file with FileImporter. Note: It's in its own library so you can reference and use in your program if you want.

Conclusion

Now if you're the acting DBA, and you put your "Junk" database on an important server, make sure you have monitors in place for disk space, CPU abuse, and excessive resource blocking. You should have this all setup regardless if you have Junk Drawer running, but especially if you do. Remember to control access to both your network share and your Junk database with Active Directory groups. 

Updates

More Configuration

 Collapse | Copy Code

  <junkdrawer>
    <types sample="100" default="string" default-length="1024" >
      <add type="boolean" />
      <add type="byte" />
      <add type="int16" />
      <add type="int32" />
      <add type="int64" />
      <add type="single" />
      <add type="double" />
      <add type="datetime" />
    </types>
  </junkdrawer>