Thanks to all for your help. I will be spending time today just working through the options. 
I should say that considering the complexity of the files and the potential...

@joe: Yes I have done it with identity column.
@scott: yes have already spoken with client about doing a lot of the heavy lifting in C#....

Hi sgmunson,
Unfortunately the data is coming from a major Pharmaceutical association in Germany. they probably have being doing it this way for decades. Not much chance lil'ole me is...

Heya Phil,
New code below.
You're right of course, ultimately I will be going for the select to pivot the columns but I am just trying to get head around...

Thanks Sergiy. When I said SQL I should have said the Grouping Query. I am guessing since I now have a ID I can find the 'Next Starting record' and...

Thanks Phil, makes sense though have to admit that even with a row number column I think the SQL is beyond me but I will give it a go.
Cheers

I just following with something I messed up on. In the format file for the TERMINATOR I only used '\n'. This meant a carriage return was added to the end...

Hi Joe Torre,
Thanks for the input. Do you think substring would be more efficient? The files I will be processing can get quite large, e.g. 500mb+. with millions of...

Thanks for the quick response sgmunson.
I hadn't noticed the SQLVARYCHAR line. This prompted me to look at the other values. When I change the xsi:type and  removed the...

Thanks... just to confirm, am I correct that to 'Server:' value would be <IP address from VPN>\ServerName.

I found some other article about this and people talked about pinging...

Hi SSC Guru
Thanks for quick reply. I am not sure what you mean by authentication method with VPN, assuming you didn't mean the SQL Server login. I have client software...

I tried to add a response yesterday but SQL central wasn't responding well.
ICACLS was returning what I was seeing in the security properties window. I did find out what...

sorry, being dense here and maybe trying to do this all wrong.

I went to my old drive (H), right-clicked, properties , Group or User Name. I can see...

Hi Eirikur Eiriksson,

thanks for your quick reply. Looking...

Hi All,

Since i haven't gotten any responses I am wondering if maybe I wasn't able to do this at my previous job. Memory does seem to be fading these days.

I...