April 24, 2009 at 8:40 am
Hi
I am newbie to SSIS 2005.
I need to pull info from two different sql servers, but not linked. (cant do that).
For eg:
I need to concatenate a single categoryId (like 101) from server1.db1.table1 to the productIds from server2.db1.table1.
Like select categoryid from server1.db1.table1.categories where categoryname = 'Apparels'
Then select categoryid + cast(productid as varchar(5)) as productuniqueid from Server2.db1.table1.products.
1. I dont know if i can join queries between sql servers which is not linked.
2. I tried storing the categoryid in a package variable. But dont know how to use that variable in select clause in sql command of oledb source editor.
like select + '[user::categoryd]' + cast(productid as varchar(5)) as productuniqueid from Server2.db1.table1.products ???
Thanks
Venky
April 24, 2009 at 8:51 am
You might be able to use a Look Up transformation for that. Take a look at that one, see if it does what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 24, 2009 at 9:57 am
Do you have a value that is the same in both databases that you could use to join? If so, you can use a merge join. Read up on it - as you will need to sort the inputs in the same order and setup the join appropriately (outer, inner, full, etc...).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 24, 2009 at 10:41 am
I tried with that and googled over it. But couldnt find a match for my scenario. Any link that points to that will be helpful.
Basically i am trying to concatenate a value (a single categoryid) from the server1 category table to all the productids in server 2 products table and then import it to server1 tablexyz. There is no id for join between these tables in these servers.
How can assign a package variable to some thing like below?
declare @CatId as varchar(5)
set @CatId = @[user::categoryid]
SELECT distinct @CatId + cast(productid as varchar) as ProductId FROM products
Thanks
venky
April 24, 2009 at 10:46 am
Basically i am trying to write a query that concatenate a value (a single categoryid) from the server1 category table to all the productids in server 2 products table and then import it to server1 tablexyz. There is no id for join between these tables in these servers.
April 24, 2009 at 5:50 pm
You can do this by using a variable and two Execute SQL tasks.
1) Create your variable first
2) The first Execute SQL task retrieves the CategoryID and assigns it to the variable (use 'Single Row' as the resultset and in the 'Result Set' area, set Result Name to be the column name of CategoryID in your query and variable name to be the name of the variable you just created).
3) The second Execute SQL task runs a parameterised query, using as its only parameter the contents of the variable, to perform the required update.
update products
set ProductID = ? + ProductID
or whatever. Mapping the new variable to the parameter is done in the 'Parameter Mapping' section. Obviously, you don't want to run this more than once, unless you can devise a suitable WHERE clause to avoid prefixing the categoryID multiple times.
April 30, 2009 at 9:14 am
Thanks for your help. I used variables in one exec sql task and used a derived column to use that variable.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply