Importing Log files with XML via SSIS

Question

Importing Log files with XML via SSIS

SG-348334

Ten Centuries

Points: 1374
More actions
October 20, 2008 at 1:53 pm

#124417

I need to create an SSIS package that will go out to each of our webblades and import our ping/post logs into our SQL Server 2005 database. It seems the fields are separated by '##' and the XML doesn't start until the 3rd position.
How can I use SSIS and parse that out into a separate field during import?
I would like to parse out all data to individual fields (including the XML data)
Name of XML Date of XML Actual XML
SoapRequest##10/18/2008 1:32:08 AM##
SoapResponse##10/18/2008 1:32:12 AM##
Actual XML -
SoapRequest##10/18/2008 1:32:12 AM##
SoapResponse##10/18/2008 1:32:13 AM##
SoapResponse##10/18/2008 1:32:13 AM##
Thanks in advance for any help
SG

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Could you attach a single file to the thread to work with?

It should be fairly straightforward with a flat file source with "##" as the column delimiter and the XML column being either string or stream that you would manipulate using a script component.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

SG-348334 Ten Centuries Points: 1374 More actions · Answer 2

Your right... I was able to use ## in the column delimiter field during import.

I was able to split the data into different fields, but now there are special characters (two squares)

that are messing up my alignment...

How would I go about scrubing and removing those characters (two squares side by side)?

Thanks

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

What column has the 2 "squares"? Odds are they are carriage return & line feed. You could use Replace to remove them.

Again, if you provided a sample file it would be easier to help.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

SG-348334 Ten Centuries Points: 1374 More actions · Answer 4

How do I remove carriage returns (double squares) from a file before importing it into the db?