spsun
asked on
Microsoft SSIS : Passing a result to a query in Data flow task
Hi All,
I'm new to SSIS. Working on a package that compares datasets from 2 different databases. I have worked on DFT that reads data from a flat file and dumps into a temptable in DB A. I need to pass a resultset ( a particular column values) from DB A to a where clause of a SELECT stmt which has to be executed on a table in DB B to pull data. Then I'll compare these 2 datasets. I need help in how to implement this using SSIS.
Thanks for your time and help..
SSISNewbee.
I'm new to SSIS. Working on a package that compares datasets from 2 different databases. I have worked on DFT that reads data from a flat file and dumps into a temptable in DB A. I need to pass a resultset ( a particular column values) from DB A to a where clause of a SELECT stmt which has to be executed on a table in DB B to pull data. Then I'll compare these 2 datasets. I need help in how to implement this using SSIS.
Thanks for your time and help..
SSISNewbee.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Thanks for response. These 2 servers are production DBs of two different systems. I cannot create temp tables or add columns to tables as I dont have write access to any of the DB objects on these servers. All I can do is only read data.
My requirement is to query tables from these 2 DBs on some common attributes then compare that data to assess data sync and report on data sync.
I approached this requirement like this: 1. Query first db and put the data in a flat file.
2. In second package I'm reading the flat file and generated a temp table in local db and then using a execute sql task followed by a recordsetdestination in which I'm populating a user defined variable with the result set.
3. On the second db, I'm executing a sql query in which in the where clause I'm passing the above defined variable which holds the result set.
The variable is populated with resultset correctly in step 2, but in step 3 the variable is not replaced with the result set and hence my where condition is failing.
I couldn't understand how to proceed further. Is this right approach to handle the above said requirement? Appreciate any responses.
Thanks.
My requirement is to query tables from these 2 DBs on some common attributes then compare that data to assess data sync and report on data sync.
I approached this requirement like this: 1. Query first db and put the data in a flat file.
2. In second package I'm reading the flat file and generated a temp table in local db and then using a execute sql task followed by a recordsetdestination in which I'm populating a user defined variable with the result set.
3. On the second db, I'm executing a sql query in which in the where clause I'm passing the above defined variable which holds the result set.
The variable is populated with resultset correctly in step 2, but in step 3 the variable is not replaced with the result set and hence my where condition is failing.
I couldn't understand how to proceed further. Is this right approach to handle the above said requirement? Appreciate any responses.
Thanks.
ASKER