Recently I was working on some ETL packages to transfer some flat file information to a SQL Server 2005 staging table. The particular package that I was working required removing some header and trailing records and then transferring the rest of the data. Some of the trailing records can only be identified by a particular junk character that appeared towards the end of the file. This resulted in me checking for this character in a Script Task in the SSIS package and then removing the line, among other things. The code for this header and trailer removing phase was around 40 to 50 lines long. The problem was the script never executed. And when I went back to check the script the entire code in the Script Task was missing! (As if some techno David Copperfield had waved his wand over my code). I rewrote the code twice over, tried saving the entire project and all kinds of silly things, yet it wouldn’t keep my code… Commenting line by line wouldn’t work either.
Only removing line by line showed me the perpetrator:
1 If str.Contains("?") Then 'ASCII Code 26 (Some fonts show it using a 'question mark' while others show a 'box')
So, myself goes and copies this character to the command prompt to see what this character actual is (This is because different types junk characters are all denoted using “?“). Guess what showed up on the command prompt: ^Z (a.k.a Undo).
Which meant, every time I saved and closed the script, the whole set of code comes undone! A quick word of admonishment to myself for not using proper standards, I quickly rewrite the script once again, but this time using the ASCII value of the character instead.
UPDATE: The above package was created using SP1 of SQL Server 2005. I just tried out the same thing with SP2, the issue seems partly fixed: The code doesn’t disappear when I close the Script Editor Window, but is cleared once I close the entire projects and open it up, unless of course the offending character is removed in its entirety…
Data Maul 