Happy to help.

Also, thanks for the follow-up!  It is always nice to hear if a solution helped or not and what the final solution ended up being.

First step would be to tell us the error as the error likely says how to fix it.

My GUESS is that you are getting a "invalid column name" error which...

Ok, I just misread the error.

If you import into memory (ie have no output step on the Excel import), do you still get the same error?

I am just thinking MAYBE...

Figured this out for you:

https://data-dev.blogspot.com/2017/03/ssis-export-data-to-excel-with-more.html

It is a limitation of the SSIS Export to Excel that 255 characters is the max.  The workaround was to export to a flat file (txt,...

My opinion - you should implement any security based things that you require.  Sounds like a dumb response, but think about it from a hacker perspective.  If a hacker gets...

I think the RTFM type responses, when written properly, can be just as helpful as asking the right questions.

I mean, if your response is "RTFM" and nothing more, then I...

Not sure if C: is the correct location to put SQL files as you are then sharing the disk I/O with the OS.

BUT one way you could do this would...

Looking at it, it is because you are comparing VARBINARY to VARCHAR.  So SQL is implicitly converting your VARBINARY(MAX) to a VARCHAR and VARBINARY 0x0A2E0A is not the same thing...

You cannot "trick" it; CSV is unformatted data.  Excel is trying to be helpful because it THOUGHT it was a numeric value.

I don't think there is any way to convince...

Not sure if the characters after the period really matter.  From reading up on the telnet email approach, I think the mail server is just looking for a line that...

Probably, but  I don't know RegEx very well and I often get it wrong so I prefer to avoid RegEx.

Also, I don't think SQL Server handles RegEx natively.

Do you need...

That would miss it.

You are getting 0A0A2E0D0A  so 0A (CHAR(10) 0A (CHAR(10)) 2E (period) 0D (CHAR(13)) 0A (CHAR(10)) which is weird to me.

One way (that is a pain in the butt but reliable) would be to cast the string as VARBINARY.

I recommend doing it with selecting the text and have the varbinary...

If the prefix and suffix you are wanting to remove are static known values, then probably the easiest way to remove them is with REPLACE:

REPLACE(REPLACE(column,'ZZZ ',''),' (left)','')

I...

Possibly a dumb question but would it be faster to do this on the application side?  Not sure how much data you are pulling (in MB/GB) if you just did...