adding column to the table

Question

adding column to the table

UnionAll

SSCrazy

Points: 2102
More actions
November 6, 2009 at 1:11 pm

#139843

Hi,
I have .txt that i am trying to load into sql table using SSIS2005
and the destination is SQL 2005.
The text file will have a header and data.
The format of the .txt will be as follows
CodeValues for the period ending 11/04/09
CodeValue
AZX123X
AZS143X
ADSE23V
This has to be loaded into a table with the date from the
header as a column
For example,
table format should be like this
CodeValue EndDate
AZX123X 11/04/09
AZS143X 11/04/09
ADSE23V 11/04/09
I am able to trim the hearder but not able to add this date column
from the header to the rows,
any ideas of to do this?
Thanks,

Viewing 15 posts - 1 through 15 (of 15 total)

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

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 1

I may give it a try if you can give the text file as attachment here, truncate the large portion of the data, so it can be a sample.

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

UnionAll SSCrazy Points: 2102 More actions · Answer 2

UnionAll

SSCrazy

Points: 2102

November 6, 2009 at 1:36 pm

#1076116

Hi,

Please find the attachment.

Thanks,

UnionAll SSCrazy Points: 2102 More actions · Answer 3

and this file have to be loaded to a table

as

Policy No Date

as the columns, and date values have to be populated from the header in the text file.

Thanks,

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

Try loading the date from the header into a variable and then add a data transformation task for a derived colum. The value in the derived column that you will add will be your variable that holds the date in the variable.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 5

Follow Jason's Suggestion that should work.

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

Phil Parkin SSC Guru Points: 246965 More actions · Answer 6

CirquedeSQLeil (11/6/2009)
Try loading the date from the header into a variable ...

That's not as easy as you've made it sound - unless you know a method I haven't thought about. Were you thinking about using a script to read the first few lines and then using 'ignore first x lines' when reading the rest of the file in a standard DF?

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

UnionAll SSCrazy Points: 2102 More actions · Answer 7

CirquedeSQLeil (11/6/2009)
Try loading the date from the header into a variable .

Can you explain a little on how to do this? Please!

Thanks,

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 8

Let me ask this... does it have to be done in SSIS? The reason I ask is because this is a trivial task in T-SQL.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 9

Jeff Moden (11/7/2009)
Let me ask this... does it have to be done in SSIS? The reason I ask is because this is a trivial task in T-SQL.

Heh... see what I mean? 😛

--===== Create a temp table to import the file to

CREATE TABLE #AllData (RawData VARCHAR(8000))

--===== Import the data into a single column using all defaults.

BULK INSERT #AllData

FROM 'C:\Temp\SomeFile.txt' --This is where I saved the example file

--===== Declare some obviously named local variables

DECLARE @PeriodEnding DATETIME

--===== Populate a variable with the Period Ending date from the file header

SELECT TOP 1 --Just to short circuit the table scan...

@PeriodEnding = SUBSTRING(RawData,PATINDEX('%[0-1][0-9]/[0-3][0-9]/[2][0-9][0-9][0-9]%',RawData),10)

FROM #AllData

WHERE RawData LIKE '%FOR PERIOD ENDING [0-1][0-9]/[0-3][0-9]/[2][0-9][0-9][0-9]%'

--===== Return a result set with the CodeValue numbers and the Period Ending date...

SELECT LTRIM(RTRIM(RawData)) AS CodeValue,

@PeriodEnding AS EndDate

FROM #AllData

WHERE SUBSTRING(RawData,2,1) > ''

AND RawData NOT LIKE ' POLICY NO%'

I suppose you could even turn it into a stored procedure with a bit of dynamic SQL so you could pass the file you want to load as a parameter and run the stored proc from SSIS... but, all things considered, why even bother with SSIS? :hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 246965 More actions · Answer 10

Nice solution Jeff.

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

UnionAll SSCrazy Points: 2102 More actions · Answer 11

Jeff Moden (11/7/2009)

Jeff Moden (11/7/2009)
Let me ask this... does it have to be done in SSIS?

Yes, because my situation is more complex than what i have showed here.

Anyway this method might work me...

Thank you so very much, I will try this.

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 12

UnionAll (11/8/2009)

Jeff Moden (11/7/2009)
Jeff Moden (11/7/2009)
Let me ask this... does it have to be done in SSIS?
Yes, because my situation is more complex than what i have showed here.
Anyway this method might work me...
Thank you so very much, I will try this.

Thanks for the feedback but, heh, you're gonna give me whiplash... 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 13

Phil Parkin (11/8/2009)
Nice solution Jeff.

Thanks, Phil. A lot of folks have asked why I haven't taken the time to learn SSIS. It's because of things like this...:-)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

UnionAll SSCrazy Points: 2102 More actions · Answer 14

UnionAll

SSCrazy

Points: 2102

November 10, 2009 at 1:28 pm

#1077479

This is perfect.

ThankYou again,

Thanks,