Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Ali Chadorbaf
Starting Member
44 Posts |
 Posted - 2008-01-24 : 13:17:53
|
| HiI have a scenario that would like to find a solution in SSIS package; this is a challenge.Question: How can I create a # table on Server2 and load it with data from Server1 and have the # table available in the whole SSIS package?Let's explain what I'd like to do:I am working with two databases that are installed on two physical servers1-Server1: database staging2-Server2: database biolap I’d like to load data from Server1 (staging DB) into a temporary table (# table) on Server2 (biolap DB) to be able to join and update # table with other dimensions located on Server2 (biolap DB); and then load # table into the Fact table located on Server2(biolap DB).- I do not want to use link server between these two servers- I do not want to use LookUps in SSIS ( as much as possible) since I need to join to too many dimensions.P.S: We are going to install staging and biolap databases on two physical servers. There are hundreds procedures in place that each one is joining these two databases. Since we do not want to use link server ( because of performance issue), we are going to convert them to SSIS packages.Thanks in advance. |
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2008-01-27 : 11:00:23
|
| Ali,I am not sure I understand what really want to achieve by using #table.You want to get data from server1 to server2. Why don't you just use regular tables?One reason maybe that you want to process in ram memory instead of paying i/o cost. And performance is a big part here. I am not sure you gain enough by going #table route to off set the cost of building the SSIS on the fly (creating index for joins etc.). DMO is capable of doing that.Another possible reason maybe that you don't know the schema of your source data till run-time. In that case, you could concatenate each row into one column then parse out later. |
 |
|
|
Ali Chadorbaf
Starting Member
44 Posts |
Posted - 2008-02-05 : 19:13:05
|
| Thanks. I go with permanent table. |
|
|
|
|
|
|
# tables in SSIS