Add data column that doesn't exist in the dataset

Question

Post reply

Add data column that doesn't exist in the dataset

ImYourDBA

SSCertifiable

Points: 5654
More actions
April 30, 2010 at 7:11 am

#146765

So, using T-SQL you can write a select query that adds a column of data to the results that doesn't necessarily exist in the table you are querying from. Such as (select 'hello' as column1, firstname from table) and it will give you back hello Jim. So, what I'm trying to do is something similar in a package in where I have a flat file that I want to import into an existing table but it only contains two of the three columns that I need. The third column I would like to specify in the package. The overall reason is that I have text files coming from 7 servers perfmon logs and while importing I want to specify which server the data was coming from. So column 1 is the datetime, column 2 is a perfmon counter, column 3 will be specified by me as the server name.
What type of transformation would I use in this situation and how would I set it up?
Thanks!
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/

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

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

Elliott Whitlow

SSC Guru

Points: 102402

April 30, 2010 at 8:01 am

#1160432

Derived column transform.

CEWII

ImYourDBA SSCertifiable Points: 5654 More actions · Answer 2

Thank you for responding. I have looked at using that and am new to SSIS. Would you be able to shed some light on how exactly I would set that up?

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

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

Hey, you're not that new! I told you how to add derived columns yesterday - see here

http://www.sqlservercentral.com/Forums/Topic912502-148-1.aspx

Phil

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

Hehe, yes I remember how to do that. My question is more around how to tell it that it should derive a column that doesn't exist in the flat file dataset that I pointed to it.

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

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

In the part where you specify what the field will contain, just add some literal text as your expression, eg

"Server1"

This will then be available to you in the data pipeline, appearing as a new field.

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

Phil Parkin (4/30/2010)
In the part where you specify what the field will contain, just add some literal text as your expression, eg
"Server1"
This will then be available to you in the data pipeline, appearing as a new field.

Well gosh, when you put it like that... It just seemed so simple. haha

Thank you very much!

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 7

Phil Parkin (4/30/2010)
Hey, you're not that new! I told you how to add derived columns yesterday - see here
Phil

That actually made me laugh outloud 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 8

Phil Parkin (4/30/2010)
Hey, you're not that new! I told you how to add derived columns yesterday - see here
http://www.sqlservercentral.com/Forums/Topic912502-148-1.aspx
Phil

:-P:-P funny

Raunak J