Flat file to Sql table

Question

Flat file to Sql table

keywestfl9

SSCertifiable

Points: 5215
More actions
July 12, 2009 at 12:15 pm

#134250

when i am trying to insert from flat file to sql table, i have to insert an additonal value which will be the month from the a column in the flat file
one of the column in the flat file is 06-24-2009
~1~333~2008-06-20~
~2~989~2009-09-23~
these data will go into sql table.
but my sql table will have a additional column which is called month and i have to extract the month from the date present in the third column from the flat file.
my table should look like
column1: row 1: 1
column2: row 1: 333
column3:row 1 : 2008-06-20
Column 4: row 1: June or 06
same with row 2
please let me know how to go about it

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

Instead of importing this data and populating a column in your table, it would be much better if you created a computed column in the table instead. Using a computed column means the value will always be correct, even if someone updates the value in the table.

Example:

CREATE TABLE dbo.MyTable (col1 int, col2 int, col3 datetime, month(col3) as col4);

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

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

Jeffrey's solution will work, but you can do it in SSIS by adding a derived column transform which will add a new column called month with a value parsed out of the date column that exists.

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

keywestfl9 SSCertifiable Points: 5215 More actions · Answer 3

Thanks Jack

i actually wanted from the date column the month and year

08-09-2009

i need August 2009

how do i do it in derived column?

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

If you want the Month Name (August) then you would be better off using a ScriptComponent than a derived column as SSIS does not have a MONTHNAME functino like Visual Basic or T-SQL, so you could only get the Month number (8 for August).

THe ScriptComponent would be a transformation, then you add an column to the output and put code like this in the script in the Input0_ProcessInputRow method:

' Replace Column3 with the column that contains your date

Row.MonthYear = MonthName(Convert.ToDateTime(Row.Column3).Month) & " " & _

Convert.ToDateTime(Row.Column3).Year.ToString()

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

keywestfl9 SSCertifiable Points: 5215 More actions · Answer 5

thanks jack

i have flat file source

data conversion

ole db destination

where should i add the script component in the flow? can you please let me know

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

Depends on what you are doing with the data conversion. But anywhere between the source and the destination.

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