September 23, 2014 at 4:52 pm
I have 3 tables-
--Server 1(SQLSERVER)
CREATE TABLE TableA (GROUP_ID INT
,STATUS VARCHAR(10))
--Server 2(Oracle)
CREATE TABLE TableB (GROUP_ID INT
,NAME VARCHAR(10)
,STATE VARCHAR(50)
,COMPANY VARCHAR(50))
--Server 1
CREATE TABLE TableC (GROUP_ID INT
,NAME VARCHAR(10)
,STATE VARCHAR(50)
,COMPANY VARCHAR(50))
Sample data
INSERT INTO TableA (1, 'READY'),(2,'NOT READY'),(3,'READY'),(4,'NOT READY')
INSERT INTO TableB (1, Mike, 'NY', 'aaa'), (1, Rick, 'OK','bbb'), (2, Smith, 'TX','ccc'), (3, Nancy, 'MN','bbb'), (4, Roger, 'CA','aaa')
I am trying to build a SSDT(SSIS 2012) package to load the data in TableC from TableB for only those GROUP_ID which has STATUS= 'READY' in TableA.
I need to accomplish this by using a project level parameter or variable for TableA->GROUP_ID and STATUS because i will be doing this for about 60 tables and those values might change.
I must build a SSIS package, it is a requirement.
using linked server is not preferred. unless its impossible to achieve through SSIS.
Any help would be appreciated.
September 23, 2014 at 10:24 pm
Use the following query for your dataflow source:
select b.GROUP_ID, b.NAME, b.STATE
from TableB b
join TableA a on b.GROUP_ID = a.GROUP_ID
where a.STATUS = 'Ready'
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 24, 2014 at 8:09 am
thanks for the response Phil, I have added some more details in the original post. unfortunately I wont be able to achieve this task with a simple join. and if you please point out the exact tables and SSDT components that I need to use, it will be helpful.
September 24, 2014 at 8:46 am
Please expand on this statement:
because i will be doing this for about 60 tables and those values might change.
Will these tables contain the same columns / datatypes?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 24, 2014 at 1:28 pm
let's say there are 30 tables in server2 like TableB and 30 tables in server1 like TableC.
let me map it.
server2-------------server1
TableB--------------TableC-----same columns
TableD--------------TableE----same columns
TableF--------------TableG----samecolumn
say there is one table in server 1 to match a table in server 2
but they will all(60 Tables) have a column GROUP_ID.
when I run the SSIS package only those records should be moved to server1 from server2 which have Group_ID that has STAUS ='READY' on TableA (on server1).
one time I may have to load the Tables in server1 for STATUS='READY' next time it might be STATUS='NOT READY'.
instead of hard coding the status inside the components like 'Lookup' or 'SQL TASK' or similar(whichever works), I would like to set a parameter where I can input the status and it applies for all the tables.
I don't know if I made it more confusing or clear!!
if you still didn't get it, let me know I will try to explain in a different way.
thanks.
October 31, 2014 at 1:46 pm
I think we need some more information.
From the little understanding I could gather, here is my suggestion:
Create a project parameter and call it Status. This is what you will use to store your Status value of Ready, Not Ready, etc. Then create a package variable that stores your extract query and call it say ExtractQuery. This variable will use an expression that combines your extract query with the Status project parameter value. Use that ExtractQuery variable to extract the data from your source and then do a lookup to Table A and then send the records that match to table C and filter out the records that don't match.
Best I could come up with based on the limited info given.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply