Whoa – Jan/Feb are gone and we are still as busy as Ninjas On Fire – not familiar with this phrase?, check out this post by the man, Scott Hanselman which explains its origins and why its so appropriate right now. Not complaining too much – if recessions have taught me anything, its gratitude to be in an industry where the work stays varied and the backlog always present. Its a problem many people would like to have.
But I know I have been busy because as every geek knows, true work is only actually being done when all 4 cores are cranking away
And left to be neglected these last 2 months? – for starters, this blog and my goal of blogging every 2 weeks to keep myself honest with my goals for the year, and all other social interaction except for our company coed volleyball team….I know there are people who have sent me LinkedIn invitations in the last few months assuming I am no longer living based on my silence – maybe tonight…..
But back to a work topic.
SSIS & Moving Data
Been spending alot of time recently moving & prepping large databases with many hundreds of tables and been using the Import/Export data option from SQL Server Management Studio to do it (ala SSIS)
And one thing that I have found is that SSIS works awesome when it works and is maddening when it doesn’t
So lets talk about the maddening stuff and I am hopeful that someone can point out the error of the ways and show me my faults as I want to believe that my problems are of my own creation
1) Can’t Import data from tables that have a column named ErrorCode because SSIS will behind the scenes implement their own ErrorCode column as they pipe the data – Now I would not add a column named that anyway as its far too generic but I do encounter it and when it happens – its crazy maddening that such a generic column name stops my data migration dead in its tracks – could not have SSIS used a slightly more SSIS-specific version of this generic phrase ? – I agree with the discussion at Microsoft Connect (found at the hyperlink above) that this is odd at best.
2) Moving data from 1 database to another and allowing it to create tables sometimes will create different datatypes in the destination table than the source table – This always especially bites me in the ass when it comes to “money” fields….I’ll be copying millions of rows from a table with some money columns in the Source table but that table when created in the Destination database will magically get created with “smallmoney” datatypes – I am assuming there is some sampling going on by SSIS and some optimization that seems to think that small money field will fit the bill, but then invariably, a single rogue dollar amount in the Source Table will blow up my migration in the Data Copying stage leaving me to curse why SSIS did not just copy the exact schema from which it is getting the data – unfortunately until I learn more (and I have searched and Binged this topic), my solution has been to script the tables in the source database first, create the tables in the Destination database and then migrate the data – however this brings up the next issue – if the tables exist, the data migrate will just append and not clear any previous data – yes this is safer but an option to let me determine whether destination tables should be appended to or overwritten would be sweet. I have the same problem with the occasional binary columns as well.
Its a powerful tool, and when it works, it makes my job easier but when it doesn’t………
Anyone else experience this? or find any good resources on how to work around these?
Any help would be appreciated as I want to love this tool.