How to import permon log file (converting Date)

Question

How to import permon log file (converting Date)

ImYourDBA

SSCertifiable

Points: 5654
More actions
April 28, 2010 at 9:30 pm

#139912

I am creating logs via Perfmon on things like Page File Byte Peak, etc. and I'm having a bit of trouble with getting them to import into a table with a datetime data type because the .csv file is being seen as a text field and throws an error. Is there something I can do in SSIS to convert the data before inserting into the table? I'm a little new to SSIS...but currently I have a package setup that works if I just import into a new table (as text) so there has to be something I can edit to convert it first.
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/

Viewing 10 posts - 1 through 9 (of 9 total)

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Yes there is. You can fire the date into a derived column transformation where you can add a new column which 'derives' a proper date from your date text.

What format is the date text in?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ImYourDBA SSCertifiable Points: 5654 More actions · Answer 2

Phil Parkin (4/29/2010)
Yes there is. You can fire the date into a derived column transformation where you can add a new column which 'derives' a proper date from your date text.
What format is the date text in?

The date is currently in 04/28/2010 12:34:13.868 format.

Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

Here's your Derived Column formula:

SUBSTRING([Column 1],7,4) + "-" + SUBSTRING([Column 1],1,2) + "-" + SUBSTRING([Column 1],4,2) + SUBSTRING([Column 1],11,13)

This will create a new column with data type Unicode String. You can map this column directly to a datetime field in SQL Server, which will look after the conversion implicitly because it's in the universal date format.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ImYourDBA SSCertifiable Points: 5654 More actions · Answer 4

Phil Parkin (4/29/2010)
Here's your Derived Column formula:
SUBSTRING([Column 1],7,4) + "-" + SUBSTRING([Column 1],1,2) + "-" + SUBSTRING([Column 1],4,2) + SUBSTRING([Column 1],11,13)
This will create a new column with data type Unicode String. You can map this column directly to a datetime field in SQL Server, which will look after the conversion implicitly because it's in the universal date format.

Thank you for sending this. I am pretty new to SSIS, could you tell me what type of Control Flow Item I need to put this into or where this needs to go in order to use it properly? Right now I have a Flat File Source and a OLE DB Destination with a Data Flow Path between.

Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

🙂 no problem. Put this between your source and destination, as part of the dataflow.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ImYourDBA SSCertifiable Points: 5654 More actions · Answer 6

Hmm...my Data Flow Path Editor only shows General, Metadata, Data Viewers. I don't see anywhere that would allow me to transform the data.

Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/

Phil Parkin SSC Guru Points: 246960 More actions · Answer 7

When you double-click on a dataflow transformation, a new window opens. In this window, you will see boxes representing your source and target.

Drag a Derived Column transformation on to this window.

Then delete the line between the source and target.

Then connect source to derived column and derived column to target (use the green 'success' lines).

Then you can double-click on the derived column to set it up.

Then you can open up the destination to modify the mappings ...

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ImYourDBA SSCertifiable Points: 5654 More actions · Answer 8

Thank you! It worked like a charm (of course) and I've learned something in the process! 😀

Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/

Phil Parkin SSC Guru Points: 246960 More actions · Answer 9

Great stuff - glad to help & thanks for posting back.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin