I bring this up because until you understand this, you're going to think SSIS was written in FORTRAN, because anytime you have to enter an expression, nothing works. Ever. For example, let's write an expression in T-SQL to strip out all occurrences of the hex 12 (0x0C, "FF") character from a string. Simple, right?
-- T-SQL code. Don't try this in SSIS. DECLARE @FF CHAR SET @FF = 0X0C DECLARE @Msg VARCHAR(50) SET @Msg = 'Hello ' + @FF + 'SSIS' PRINT @Msg SET @Msg = REPLACE(@Msg, 0x0C, '') PRINT @Msg
The output, depending on your font, collation, codepage, which version of Windows you're using, and hat size, will look something like this:
Hello §SSIS Hello SSIS
So, we've proven we know how to use the REPLACE function in T-SQL. Admirable, but it doesn't help us in SSIS. That is, this doesn't work:
REPLACE(SampleBeg, 0x0C, '')
Try that expression there, and you'll get the usual non-helpful error SSIS message stack. For some reason, SSIS likes double-quotes, not single-quotes, and of course has its own unique way of expressing character constants in hex:
REPLACE(SampleBeg, "\x000C", "")
I'm sure there are excellent reasons why SSIS is such a Frankenstein's monster. When you're building something that might not be such a good idea to begin with, you're going to find some funky design choices forced on you, and my guess is that's what happened with SSIS. All I know is, the way it's been designed, it will be a long time before I can get it to do what I want.