How to get 2 data sources to merge in SSIS?

Question

Hi,

I am fairly new to SSIS. I came across a situation where i have to use a data flow task. The data source is MS- SQL server 2008 and destination is Sharepoint list. I gave an SQl query for Data source object as

SELECT     Customer_ID, Project_ID, Project_Name, Project_Manager_ID, Project_Manager_Name, DeliveryManager1_ID, DM1NAME FROM dbo.LifeScience_Project
WHERE (Customer_ID IN ('1200532', '1200632', '1207916', '1212121', '1217793', '1219351', '1219417', '1219776'))

Now, this is the problem. The customer ids in where clause need to come from a different data source. That would make it look like something as

SELECT     Customer_ID, Project_ID, Project_Name, Project_Manager_ID, Project_Manager_Name, DeliveryManager1_ID, DM1NAME FROM dbo.LifeScience_Project
WHERE Customer_ID IN (select customer_id from [Database2].Customer_Master)

Please guide me to implement this.

Answer 1

You would have to use a Lookup Component within your data flow task. So for example, write your first query within an OLEDB Component by chosing SQL Command from the Data Access Mode just pulling everything, i.e.,

SELECT Customer_ID, Project_ID, Project_Name, Project_Manager_ID, Project_Manager_Name, DeliveryManager1_ID, DM1NAME
FROM dbo.LifeScience_Project

Then connect this source to a Lookup Component and within the Lookup component, instead of choosing a lookup table, select the SQL option and type your second query in there:

select customer_id
from [Database2].Customer_Master

Now do a lookup matching the two Customer_ID fields and only direct output on successful matches. If there are specific ID's you want you can add that to the second query like so:

select customer_id
from [Database2].Customer_Master
WHERE customer_id IN('someid', 'someid2',...)

That's how I would do it.

EDIT:
In response to Sushant's additional questions for clarification.
(1) You want to use the MATCH output when directing your rows NOT NO MATCH.
(2) Yes that is correct, make sure for your data source it points to the other database (Non-SQL2008 one).
(3) In columns Tab you simply match Customer_ID to Customer_ID;
(4) In Advanced tab you don't need to do anything. In the Error tab you can choose to Ignore errors since you don;t care about the non-matches.
(5) Yes that is the correct flow - the prompt when connecting to Sharepoint is asking if you want to redirect the MATCHED output from the Lookup or the UNMATCHED output. You want to choose the MATCHED output as I mentioned in point (1).

Answer 2

I would run one query in an execute sql task in which build you a list of customer Id's and store the result in a variable:-

Declare @s varchar(max)

Set @s ='' SELECT @s = @s + '''' + Cast(customer_id as varchar(20)) + ''',' FROM (select customer_id from [Database2].Customer_Master ) As T

Select @s

Then in your dataflow task, the source query would be parameterized, using the variable from the first part.

SELECT Customer_ID, Project_ID, Project_Name, Project_Manager_ID, Project_Manager_Name, DeliveryManager1_ID, DM1NAME FROM dbo.LifeScience_Project WHERE (Customer_ID IN (?))