My First Rant - Importing Tables

So the server arrived...finally. I log into the Microsoft Sql Server Management Studio, by now having gotten some experience with it through use in Microsoft Sql Server Express.

Oh by the way - if you're not needing to do a bunch of db admin stuff - using the Express is a great way to get familiarized with the basic layout of the Management studio - half the battle here folks.

I'm not expecting the world so I start to look into porting over the tables from mySQL to Microsoft. I create the new database and right click on the little drum - "Tasks" allows me to import a table. This is pretty cool. I've got my MyODBC driver loaded - a nice slow but reliable connector - I select the ODBC option and it asks me for a DSN or connection string.

At this point - you could add the string, or just create a user DSN which is much easier to change later if needed. Plus, you can test the connection properties and get a response for those people not speaking hexcommando language if it fails.

Once I get this set up, its pretty nice - choose select * from whatever_table and you've got your import - make sure to check the column mappings though to make sure no silly truncating through the translation.

Next - if that step went right - it will ask you whether to execute now or create an SSIS package. Trust me - unless your project is small - choose to create an SSIS package - it allows you to not have to enter all the bs before and allows you to re-use the package for implementing db mirrors just by changing the DSN above.

Oh yeah - save it to the filesystem.

So lets see if the process works. If all goes well - it works. If not - you get a lot of nonsensical and totally useless errors which spew from the first totally useless error telling you something went wrong and giving you a hexidecimal error code. Thank god for google - get ready to start examining field values in your original table for something slightly out of the "acceptable" range for MSSQL.

Things I've found it not to like mostly include dates and longer text columns which could be nvchar or ntext depending on which side of the brain is currently controlling you.

In the worst case - SQL2005 is complaining about a constraint on a column definition you gave it - kind of ironic don't you think? It lets you put in an invalid value for a constraint to prevent users from entering an invalid value!