August 7, 2013 at 1:31 pm
Here is my situation. I have to pull data from two different servers that are not linked together. For that, I will have to use SSIS to create a package to pull down the data. In server A, there is a staging table and that is where I will pull the data from two different servers into.
Here is what I have so far in SSIS package. In Control flow, I added an Execute SQL Task to get the necessary information from multiple tables in Server A and put it into the staging table in Server A. Now, I need to use the list of IDs from the staging table to query a table in Server B and pull the results into the staging table in Server A.
I am fairly new to SSIS and I think the only way to do this is to use Script Task as that is the only task that can connect to two different servers at the same time. Do you have a better suggestions or ideas?
August 7, 2013 at 1:54 pm
Now, I need to use the list of IDs from the staging table to query a table in Server B and pull the results into the staging table in Server A.
Can you elaborate a bit more on this part?
Some sample data and desired results would be nice.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 7, 2013 at 1:58 pm
Script Task is one way.
another way is using variable to store the list of ID, then access server B based on the variable. So you don't have to access both databases at same time.
August 7, 2013 at 2:08 pm
SillyDragon (8/7/2013)
Script Task is one way.another way is using variable to store the list of ID, then access server B based on the variable. So you don't have to access both databases at same time.
The values are too big for the variable. If I remember correctly, there is max of 2,000 chars for expressions.
August 7, 2013 at 2:09 pm
Koen Verbeeck (8/7/2013)
Now, I need to use the list of IDs from the staging table to query a table in Server B and pull the results into the staging table in Server A.
Can you elaborate a bit more on this part?
Some sample data and desired results would be nice.
Something like this.
Select fieldA, fieldB
from ServerB.table
where id in (select id from SerberA.StagingTable)
August 7, 2013 at 2:13 pm
ugh3012 (8/7/2013)
Koen Verbeeck (8/7/2013)
Now, I need to use the list of IDs from the staging table to query a table in Server B and pull the results into the staging table in Server A.
Can you elaborate a bit more on this part?
Some sample data and desired results would be nice.
Something like this.
Select fieldA, fieldB
from ServerB.table
where id in (select id from SerberA.StagingTable)
I don't know how big your tables are, but you can do this easily with a lookup component.
Read ID, fieldA and fieldB from ServerB into the data flow. Connect to a lookup component and select ID from server A and map this to the ID of server B.
Only keep matched rows, redirect non-matched rows to the no-match output. Connect the match output to the staging table on server A.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 7, 2013 at 2:23 pm
Koen Verbeeck (8/7/2013)
I don't know how big your tables are, but you can do this easily with a lookup component.
Read ID, fieldA and fieldB from ServerB into the data flow. Connect to a lookup component and select ID from server A and map this to the ID of server B.
Only keep matched rows, redirect non-matched rows to the no-match output. Connect the match output to the staging table on server A.
I did not know about the lookup component. I will try that tomorrow. Thanks.
August 7, 2013 at 2:28 pm
ugh3012 (8/7/2013)
Koen Verbeeck (8/7/2013)
I don't know how big your tables are, but you can do this easily with a lookup component.
Read ID, fieldA and fieldB from ServerB into the data flow. Connect to a lookup component and select ID from server A and map this to the ID of server B.
Only keep matched rows, redirect non-matched rows to the no-match output. Connect the match output to the staging table on server A.
I did not know about the lookup component. I will try that tomorrow. Thanks.
I mentioned before that the size of the tables can be important. The lookup table will cache all the IDs from server A. If you have millions of rows, that could be an issue if you don't have enough RAM. (but I guess you'll be fine)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 7, 2013 at 5:17 pm
August 8, 2013 at 12:42 am
mario17 (8/7/2013)
How about linked servers?http://technet.microsoft.com/en-us/library/ff772782.aspx
This should work
M
Linked servers bring a whole world of pain with them.
The lookup solution will be more performant.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply