September 1, 2014 at 7:30 am
I am a complete newbie to SSIS. I can create a simple package to transfer data between SQL instances and thats about it.
I have tableA (source data) and tableB (Destination data). TableA has 4 column and tableB has 5. I want to transfer all of the columns from tableA into TableB, but the 5th column in tableB needs to be populated with the Server\Instance name of the server TableA sits on. Do I need to have multiple data sources to achieve this? I have tried but no matter how I set it up, the Column in the destination is set to ignore.
Any help will be appreciated
September 1, 2014 at 7:45 am
Multiple ways to do it.
Easiest would be to change the source to be a query instead of a table and add in the @@servername parameter
SELECT
col1,
col2,
col3,
col4,
@@servername as col5
from
table1
And then remap the DFT to include the new column.
You could always look at variables.
A good place to start for all things SSIS would be the stairways section on the left menu.
September 1, 2014 at 7:48 am
answered my own question whilst typing that out!
SELECT (select SERVERPROPERTY ('ServerName') ) as [Connection String], [Database Name], [Recovery Model], [Compatibility Level], State, Owner
FROM [Database State]
WHERE ([Database Name] NOT IN ('master', 'tempdb', 'model', 'msdb'))
appreciate your time
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply