Hello, I have below data: SubsidiaryId SubsidiaryName CountryId CountryName CurrencyCode --------------- -------------------- ----------------- - ------------------ -------------------- 1 United States 20 United States USD 1 United States 34 America Samoa USD 1 United States 37 U.S. Minor Islands USD 2 Canada 22 Canada CAD 2 Canada 30 St. Pierre CAD 3 India 28 India INR And I have target with below columns ID SubsidiaryId SubsidiaryName CountryId CountryName CurrencyCode Cost --------- ------------- -------------------- ----------------- - ------------------ ----------------- --------- 1 1 United States 20 United States USD 10 2 1 United States 34 America Samoa USD 10 3 1 United States 37 U.S. Minor Islands USD 10 4 2 Canada 22 Canada CAD 8 5 2 Canada 30 St. Pierre CAD 8 6 3 India 28 India INR 5 Now my target table should get sync from source but theCost should not be updated as not available in source but rest of the column may updated/new records can be added too. So how do I create my DataFlow task to achieve this? - So new Subsidiary can be added in Source - New Country can be added under subsidiary - Old Country Name can be changed/removed under subsidiary Please advise. Thanks, Prabhat

Hello, 1--Extract data from your source (SQLDB,Excel,FlatFile etc) 2--Use Lookup, use this query( Select distinct SubsidiaryId from Dbo.TargetTable), Map SubsidiaryID from your source and map in Lookup to SubsidiaryID. Use the not matching output of lookup and insert into target table( This will cover this point - So new Subsidiary can be added in Source) 3--get the second lookup, use this query (Select distinct CountryName from dbo.targettable), use the matching output of first lookup as input to this lookup. use non matching records and insert into dbo.target table. 4--Use oledb command transofmration , Update the countryname depending upon subsidiaryid. Update dbo.targettable set CountryName=? WHERE SubsidiaryID=? OLEDB Command transformation is Asyn transformation( means row by row operation) that means slow processing. You can create a stored procedure that can handle all the points and use in Execute SQL Task. If you are using SQL 2008, you can use Merge statement to handle insert/update. Thankshttp://sqlage.blogspot.com/

IF you want to use TSQL then here is draft code, you can make changes according to your requirements. Create table #TSource(SubsidiaryId INT,SubsidiaryName VARCHAR(50),CountryId INT,CountryName VARCHAR(50),CurrencyCode VARCHAR(50)) go Create table #TDestination(ID int identity(1,1),SubsidiaryId INT,SubsidiaryName VARCHAR(50),CountryId INT,CountryName VARCHAR(50),CurrencyCode VARCHAR(50), Cost int) go insert into #TSource values(1,'United States',20,'United States','USD') go insert into #TSource values(1,'United States',34,'America Samoa','USD') go insert into #TSource values(1,'United States',37,'U.S. Minor Islands','USD') go insert into #TSource values(2,'Canada',22,'Canada','CAD') go insert into #TSource values(2,'Canada',30,'St. Pierre','CAD') SELECT * from #TSource --SELECT * From #TDestination Create proc #SP as --INSERT NEW SubsidiaryID INSERT INTO #TDestination(SubsidiaryId,SubsidiaryName,CountryId,CountryName,CurrencyCode) SELECT * FROM #TSource S WHERE Not exists (SELECT 1 from #TDestination D WHERE D.SubsidiaryId=S.SubsidiaryId) --INSERT NEW COUNTRYNAME INSERT INTO #TDestination(SubsidiaryId,SubsidiaryName,CountryId,CountryName,CurrencyCode) SELECT * FROM #TSource S WHERE Not exists (SELECT 1 from #TDestination D WHERE D.CountryName=S.CountryName) Update D SET D.CountryName=S.CountryName FROM #TDestination D inner join #TSource S ON D.SubsidiaryId=S.SubsidiaryId AND D.CountryName<>S.CountryName http://sqlage.blogspot.com/

Hi Aamir... I will try the SSIS method now and let you know... I have tried with a single Lookup and used select * from target table and mapped SubsidiaryId and that ws not working... I will try your suggestion now... Thanks for your input.

Please don't use * as you don't need all of the columns, * will bring everything from target table and that can hurt the performance.Always use the columns you need them. Lookup is case senstive , Make sure you convert to Same CASE(upper, lower) source and destination and Trim them to remove spaces. Thanks http://sqlage.blogspot.com/

Yes True. I am doing that... Some query on your way of implementing: 1--Extract data from your source (SQLDB,Excel,FlatFile etc) I have done this. From SQL Server. 2--Use Lookup, use this query( Select distinct SubsidiaryId from Dbo.TargetTable), Map SubsidiaryID from your source and map in Lookup to SubsidiaryID. I have one Lookup and used the above query. And mapped the Source with target SubsidiaryId. Do I need to add the lookup column in the below grid? if Yes for what column? As the target has one column. Use the not matching output of lookup and insert into target table( This will cover this point - So new Subsidiary can be added in Source) Thanks, Prabhat

You don't have to add lookup columns as you will only insert new records from Source. Non matching output of lookup will provide you all new records from source those are not in target. Thankshttp://sqlage.blogspot.com/

Thanks Aamir for the help. I have done that and now in 2nd Lookup and used the query you suggested and taking the error outout for Insert to target Table. But I am getting warning as below: no rows will be sent to error output. Configure error or truncate disposition to redirect the rows. Thanks. EDIT: And it failed with: [Lookup [2717]] Error: Row yielded no match during lookup. Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Lookup" (2717) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

This error is due the unavailability for the source value in the lookup data. I would suggest, you can use SCD for this. It is just an option, you can try and it may make your life easier.Thanks Ayyappan Thangaraj UG Lead, Puducherry, http://SQLServerRider.blogspot.com

Thanks Ayyappan for the response. Yes that indead helped me to find the issue :) Now I have used one Lookup transformation and one conditional split transformation and get my work done, as my case was Type 1 SCD so this helped me :). BTW: I do need to learn the SCD as well ;) Thanks.