Problem intrasnfering data from log file to tables

Question

Problem intrasnfering data from log file to tables

arctica82

Ten Centuries

Points: 1013
More actions
April 6, 2009 at 12:42 am

#207980

I have DTS in SQL SERVER 2000 that I have rewritten it as SSIS in sql server 2008,In this SSIS I am trying to transfer data from log file to table.I have used the same setting that I had used in DTS.But the Column delimiter(Which is Comma) does not work properly, and does not detect the columns correctly.
I would appricate if someone help me on that.

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

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

gtschech Ten Centuries Points: 1351 More actions · Answer 1

Grasshopper,

if you use a Flat File Data Source, you can run into problems with comma if your Locale settings use the comma as decimal delimiter.

If you create a new Flat File connection, yout should see the data interpreted on the "Columns" page; if this shows the right data, you should be able to proceed.

If that doesn't help, it would be good to provide some more detail on your problem...

mfg

Günter

arctica82 Ten Centuries Points: 1013 More actions · Answer 2

Thanks alot for your reply,Even when I added Flat file connection instead of Flat File Data source,still I have the same issue.Instead of recognizeing all columns(6 columns) .I have only 3 columns(Which is completely messed up).Actually I have the same problem in SQL SERVER 2008,but in SQL SERVER 2000,I do not have any issue,although I have used the same settings for Flat file connection.I would appreciate if you suggest any solution for that.

Thanks

arctica82 Ten Centuries Points: 1013 More actions · Answer 3

Here is sample data that I have in log file:

03/04/2009 08:01:15:215 Create Session: 011.202.111.123, 502184, ed5cb1c53cdd6fa2c19a2e84c493te0c3e09d731603/04/2009 08:01:15:324 New page access: 011.202.111.123, 0, 502184, 0, 0, b1c53tcdd6fa2c19a2e84c493e0c3e09d7316ae21c025e4f

03/04/2009 08:01:15:512 New page access: 011.202.111.123, 0, 502184, 0, 0, b1c53tcdd6fa2c19a2e84c493e0c3e09d7316ae

and the setting for Flat file connection is as faollows:

In General Page:

Locale:English(United states)

Code page:1252(ANSI-Latin I)

Format:Delimited

Text Qualifier :

Header Row Delimeter:{CR}{LF}

In Column Page:

Row Delimiter: {CR}{LF}

Column Identifer:Comma {,}

But now in the preview,instead of having 6 columns,I have 3 columns(They are completely messed up)

Also ,How can I filtered data that I have in Flat file Connection,For example I just want to filter only the rows that their second column is equal to 502184 and then import theses filtered data to table.

gtschech Ten Centuries Points: 1351 More actions · Answer 4

Thanks for your sample data, that clarified your problem.

The Flat File Data Source parses the first line of data to learn ab out the format of your data.

Your first line, however, consists of three comma-separated entries, so the FFS makes three columns.

When it comes to the next lines, it finds more commas, but disregards the rest.

You get a better behavior, if you tell the FFConnectionManager to skip the first line ("Header rows to skip" in "General" page).

Then switch to "Columns" page and press "Reset columns" and voilà, you got 6 columns.

But beware that the layout of your flat file is different in the "Create Session" row from the other two rows; the three columns with zeros are missing. This will need additional effort.

Just a sketch:

You'll need a Dataflow Task with one Flat File Source (that only takes string columns!), than have a consitional split that separates the rows by their names ("Create Session", "New page access" etc.) and then following some Derived Column tasks which convert the respective rows to their appropriate format for passing them on to some database table.

This would also be a good place to insert a filter, which is applied as a "conditional split" task, where you just catch the matching rows.

Hope that helps

Günter