adding a column to data import

Question

adding a column to data import

alen teplitsky

SSC-Dedicated

Points: 30014
More actions
March 23, 2010 at 9:30 am

#138892

i have a process of reading data from a database that collects perfmon counters to see the replication latency and how many transactions we're behind. there is a bug in SQL server and i need to change this to collect data from sp_replcounters.
problem is that i want to import sp_replcounters from several servers to a single table to run reports from and need a server name column.
i have a data flow task set up to run the import from each server. How do I add a server_name column to be populated? I tried Union All but it didn't work. i can run an update after each import, but wonder if there is an easier solution

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

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

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 1

Since you have a separate import for each server and just have to add the Server Name.. Did you try using Derived Column to add your server name?.. each import will need one Derived column where you can enter their respective server names..

Phil Parkin SSC Guru Points: 246963 More actions · Answer 2

Or add @@SERVERNAME to your SELECT statement?

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

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 3

Phil Parkin (3/23/2010)
Or add @@SERVERNAME to your SELECT statement?

how would i do that? the source of the data flow is exec sp_replcounters

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

You have to do it as a derived column. But I think you have a larger problem. It is difficult to use a sproc as a source of data for a datasource.

CEWII

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 5

got the derived column to work. haven't used SSIS much.

data source is the sp then it goes to the derived column that adds the column server_name and populates it with "server_name" and then the destination table

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

SQL Noob (3/23/2010)
Phil Parkin (3/23/2010)
Or add @@SERVERNAME to your SELECT statement?
how would i do that? the source of the data flow is exec sp_replcounters

🙂 er ... perhaps I should have considered my response for a greater length of time - you can't, unless you hack the sp.

But glad to hear that you got it working.

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