I was working this weekend with a fairly simple Excel spreadsheet, and I had to decompose one cell in it out to three columns in a SQL Server table. There are tools within SQL Server Integration Services (SSIS) that should be able to do that, but I just couldn’t find my way around them properly.
I’m not as familiar with SSIS as I would like to be – it’s just not my day-to-day tool. I was struggling with the split of the cells, since it had several different types and kinds of delimeters for the data. I knew how to do it with regular expressions, I knew how to do it with Transact-SQL, I knew how to do it with lots of programs, but I just couldn’t figure it out in SSIS. I’m sure that’s my fault – not the fault of the tool, by the way.
I spoke with the person that created the spreadsheet, and it turns out that he had combined those fields from his source to begin with! In other words, he could split them out for me. What a relief! Yes, I need to come back some time and figure out how to beat this, but right now I just needed to get the work done. He split the cells out for me in the spreadsheet (took him about 3 minutes) and then I had the three discreet cells I was looking for.
Sometimes it’s OK to take a shortcut. As long as you think it through, you can safely do things the “easy way”, when you’re careful and understand what the ramifications are. The key here is that this was a one-time import, I understood both sides of the data, and had done my research. The data itself didn’t change; just it’s cell location. And that was all OK.
So don’t make it harder on yourself than it needs to be. Consider the alternatives – and take the help where you safely can.