Two Things

Gaming and Programming.. maybe programming for some games, who knows. Either way, I'm a geek, right?

SQL 2012 Import - Unknown column type conversion

Importing a flat file (CSV) into MS SQL 2012 should be a straightforward task, one would think.  That is, until you run into a brick wall that reads "Found 1 unknown column type conversion(s).  You are only allowed to save the package."

Thanks, SQL.

The problem, if you bother to look at the details of the offending column (it will be the one with the big red X next to it), is that SQL has no idea how to convert from the incoming column - usually DT_STR - into your desired destination column.  For me, my destination was a datetime2 column which seems to be DT_TIMESTAMP2 in the SQL conversion world.

All of this is powered by an XML file located called DtwTypeConversion.xml located at either C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn or C:\Program Files\Microsoft SQL Server\110\DTS\Binn  (older/newer version of SQL might use it also but they'd be in a different subfolder obviously.. if in doubt, search for the file).

Inside that file if you look down for a SourceType of DT_STR you will see all of the types it can convert to, and the "steps" for converting them.  You will see an entry for DT_TIMESTAMP but not one for DT_TIMESTAMP2.

Add one.

The conversion looks like you can configure several steps if you need to convert to intermediary datatypes before getting to your end goal.  For me, one step was enough.  I just copied the entry for DT_TIMESTAMP and changed it to DT_TIMESTAMP2.

After that, you will get the regular yellow exclamation point instead of the big red x, and you can run your import and move on to solving the next error.