February 1, 2012 at 12:00 pm
I am task with identifying the source database name, id, and server name for each staging table that I create. I need to add this to a derived column on all staging tables created from merging same tables on different servers together.
When doing a Merge Join, there is no way to identify the source of data so I would like to see if data came from one database more than the other servers or if their are duplicates across servers.
The thing that bugs me about SSIS Data Flow task is there is no way to do an easy Execute SQL Task after I select my ADO.NET Source to get this information because my connection string is dynamic and there is no way of know which data source is being picked up at runtime.
Please help.
For Example I have Products table on Server 1 and 2:
Server 2 has more Products and would like to join the two together to create a staging table.
I want see the following:
Product ID, Product Name, Qty, Src_DB_ID, Src_DB_Name, Src_Server_Name
1 IPAD 1000 2, MyDB1, Server1
100 ASUS Pad 40 1, YourDB, Server2
Can someone help me get database name and server name in DATA FLOW only (without using a for each in Control Flow)
February 1, 2012 at 1:00 pm
set up a view that includes the global @@servername as one of the columns in the select. Reference this view from your SSIS task.
The probability of survival is inversely proportional to the angle of arrival.
February 2, 2012 at 8:09 am
I need to identify the source db when I am inside the Data Flow
I have a merge join task that joins two server tables together after being sorted, problem is there is no way to determine which records was kept for what server.
Server 1
Database Name: Adventure
Table: Products
Record 1 Tablet 100 qty
Server 2
Database Name: Trails
Table: Products
Record 1 Tablet 100 qty
Record 2 PC 50 qty
-- Get Server Name and database name before executing MERGE JOIN task --
End Results of MERGE JOIN that I would like to see:
Server 3 (final server)
Database: Adventure_Trails
Table: Products:
Record 1 Tablet 100 qty Server 1 Adventure
Record 2 PC 50 qty Server 2 Trails
I want to see the server name and database name after Merge Join
Problem I don't know what task other than Script Component I can use to get this information. I wished Microsoft provide some system variables with this information to make it easy. Machine name doesn't help me because I am connection my data source to other computers and Machine name only returns the name of the computer the SSIS is currently running on.
My first thought is to use Package Configurations - problem there is if I dynamically retrieve my connection manager, there is no way for me to identify the specific server and database name before the process hits the MERGE JOIN task.
Please help!
February 2, 2012 at 2:09 pm
Can someone help with this question?
June 21, 2015 at 8:25 pm
Hi guys,
I am facing similar request. Can anyone help?
Grasshopper, have you found any solutions yet?
Thanks 🙂
June 22, 2015 at 5:35 am
i liked the suggestion above, to create a view which includes @@Servername and db_name() as columns.
did you try it, or can you say why that will not work for you?
Lowell
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply