When I needed to create an import system, I decided to use SSIS. The documentation for the SSIS API is very limited. This blog provides some good samples, but was still incomplete. Only after nearly finishing this project did I discover this new easy button. The easy button may be the way to go for most tasks, but coding directly against the API will certainly give more control and is probably absolutely necessary in many cases (I doubt the easy button is going to allow configuration of columns in a Flat File Source).

Anyway, I put a good amount of time into discovering how to accomplish this stuff, so it seems likely to save some effort for other folks.

4 different types of packages may be created and written to a file or executed. Each package will contain only one Data Flow Task. The Data Flow Task will contain one data flow source, one data flow destination, and may contain one data flow transformation:
  • Delimited Text: source -> destination
  • Fixed Width Text: source -> Derived Column Transformation (to trim columns) -> destination
  • Excel: source -> Data Conversion Transformation (to convert type) -> destination
  • XML: Same above

The main public class is ImportDirector. After running, it will contain:
  • The created table name. This will be a global temporary (##) table with all columns of type varchar. ImportDirector has a method for releasing the table.
  • A list of columns (though the actual table will also have a primary key column)
  • A list of messages generated by the SSIS runtime.

To test it out:
  • Open app.config.
  • Set the connection string within.
  • Open Program.cs.
  • Set connection string named "SSISConnectionStr" to the same database (see comments for explanation of the duplication).
  • Uncomment one of the option declarations for the appropriate file type.
  • Set the properties of your uncommented options variable.
  • Set the value of variable runnit. True will execute the package. False will write it to disk (see comment).
  • Run it.

Dependencies:
  • Visual Studio 2010
  • SSIS: API assemblies installed with Sql Server 2008
  • ACE driver (I think this gets installed with either Office 2007 or 2010). If not, it's available here
See the class diagram (ClassDiagram1.cd) for overview of classes.

Last edited Nov 4, 2011 at 4:14 PM by bradwood, version 5