As you may have noticed, or not noticed, I have not done anything blog-worthy in quite a while. Well, actually that is not true, I have done a few things in Windows PowerShell that I think are kinda cool but the blog post is still swimming around in my mind and I have not sat down at typed it out, yet. But anyway, I did something this morning that I bet will pop up again and I think I should put it somewhere what I can find it later. So you get to hear about it also. I was building a fairly simple SSIS package. Select data from a database and write it to a text file. They don’t get much easier in my experience. I was using < CR >< LF > as my new row indicator. That seemed to work and I was going on to my next project. The customer called me. It seems that embedded in two of the columns (a text field) the users could and did use < CR >< LF > to format the text in the field. Well that was really messing with my extract. Never fear, dear readers, there is a solution. My good friend Mr. Google (or maybe Mr. Bing) let me know that char(13) was a carriage return and char(10) was a line feed. Well that made it easy. I edited my select statement to look something like this:
,replace(replace(dbo.table.column3,char(13),’’),char(10),’’) as ‘column3’
,replace(replace(dbo.table.column4,char(13),’’),char(10),’’) as ‘column4’
ORDER BY dbo.table.column1
I no longer had < CR >< LF > inside the column impacting my extract. Everyone was happy.