I actually need to automatically generate this file on a daily basis and upload it to an FTP site. I'm hoping that I can accomplish this whole process using SSIS....

I'm also having a hard time appending this to the top of the file:

<?xml version="1.0" encoding="ISO-8859-1"?>

Any suggestions...?

Thanks Steven! I've been able to get almost everything formatted the way I want by using some of your examples.

Now I just need to append a separate <control> element...

Thanks! I actually just found that same function. 😀

It looks like it will handle almost anything...

Let me rephrase that...I'm actually looking for the total number of product *rates* for each product category. That's why I've included the custrate table in the example. A count...

OK... Here goes...

The rateheader table has all of the unique rates for all products. The rate table has all of those same base rate records with idnum = 0....

I was able to hack it all together with C#.

The following assumes that you know a little bit about using Visual Studio and programming in C#.

1) Create a subscription...

OK...OK... Not so "Advanced" after all. For you maybe... 😛

Here's the finished product:

SELECT SUM(CAST(SUBSTRING(col001, 2,10) AS INT)) Amount,

MAX(SUBSTRING(col001, 12,21)) Element1,

MAX(SUBSTRING(col001, 33,29)) AS UserName,

MAX(SUBSTRING(col001, LEN(col001) -19, 19...

Thanks Sergiy....

And you're right...that part is/was easy. I pretty much figured out how to do that.

The problem that I'm having is rewriting the string back to it's original and exact...

Yeah...I do understand...kinda. 😉 I like the CTE solution. I was just thinking that there was a simpler way with less code.

Thanks Bruce! I'll give it a try.

Thanks Flo!

That worked great.

It will always be: * " ; Serial" with any given amount of spaces or tabs between the date, semicolon and "Serial."

Thanks for the reply Flo!

Unfortunately...the white space in zone file can be tab characters or any given number of white spaces.

The expression evaluates now. However...I get this error.

TITLE: Package Validation Error

------------------------------

Package Validation Error

------------------------------

ADDITIONAL INFORMATION:

Error at File System Task: Failed to lock variable "D:\20090519-acct.csv" for read access with error 0xC0010001 "The...