That's right it's not. But seeing as I was specifying what the row delimiter was I assumed that FIRSTROW=20 would be ultimately the 20th linefeed; and that it would break...

So, to conclude: I ended up cleaning the file using powershell by extracting only the lines starting with the token that I needed and writing them to a new file

Chris, yes, that ties in with what I am seeing.

This is a hassle!

I am trying to avoid having to clean the file before hand, but its...

Hmmm, the above comment from me, seems to be what I am seeing. If I specify row 2 as the starting row then it loads the rest in fine (from...

if I open the input.txt file in notepad++ it looks like this:

In notepad though it only has 3-4 lines because its not breaking on the...

That's correct, about 17-18 rows of header stamp from the source, followed by a header row, followed by data... 2 rows.

When I run either of the t-sql I...

Alejandro, I don't think that is what I mean.

Perry, yes this is what I intend.

Certain users 'forget' to append ApplicationIntent to their connection string. This results...

Thanks Sue.

Yes, I am filtering after the query then. As the where clauses exists outside of the OpenRowset query.

Which makes it not suitable, unfortunately, for my...

Cheers Thom.

Fortunately our security man just picked holes in their proposed solution anyway, so its all going back to the drawing board for something less cobbled together.

Cheers Thomas!

Hi Michael,

Unfortunately our Administrators are spread pretty thin, and as we have over two thousand jobs (over multiple instances)  and we also have users who want to...

Wow, that's awesome -- the $(ESCAPE_NONE(JOBID)).

The only problem I can foresee with adding this as a last step is that during the time it looks up the...

For completeness and for anyone following or on a similar mission to me, below is how I ended up using SqlPirate's (above) T-SQL.