.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

How to Sync Source and Target table here?

Posted By:      Posted Date: May 22, 2011    Points: 0   Category :


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/rem

View Complete Post

More Related Resource Links

"Non-additive" target value in KPI. How to present it in a scorecard or Pivot Table.

Hi.. I would like to have some help in designing the following solution. It musn't be to hard, but i can't figure it out yet.. I have 2 tables: Group and Worker. Table Group has GroupID and GroupTarget. Table Worker has WorkerName, GroupID, WorkerTarget and Amount. The relation between tables is GroupID, as each worker belongs to a group. Now I want to take this to a pivotTable, to present KPI for each worker, and the value of the indicator is calculated comparing the Amount vs the WorkerTarget. Also I setup a single hierarchy in the PivotTable, where the parent is the GroupID, and when displayed, you can see all the Workers belonging to that group. Now the problem is, Everything is fine when I want to see the actual group total amount value, as I just have to SUM all the Amounts of each Worker, and I can see the GroupAmount. But.. ¿How can I display the GroupTarget in the PivotTable in the same column as the WorkerTarget? I don't mind a solution using SSAS or PerformancePoint, but my idea is in SSAS to put some kind of formula in the KPI target, so when I'm in a level of the hierarchy, the target is WorkerTarger, and when I'm in the parent, the target is GroupTarget. Is there some kind of formula like this??   If not, any other idea will be much apreciated.. Thanks a lot.. regards.. Canario O.

Multiple Source connections in Configuration Table

 I am trying to query multiple servers for properties and record those properties in a table for later use.  I can make this work with the ForEachEnumerator by querying a table. However, I would like to use the Package Configurations option if I can instead.  Any options? Is there a way to force the package to loop through multiple values for one variable, contained in an SSIS configuration table?  Thanks in advance!

Is there anyway to Sync SharePoint 2010 table to MOSS 2007 table?

Hello, I would like to know what can i use to sync a sharepoint 2010 table to a sharepoint 2007 table? Also could this be done by pulling the webservice for sharepoint 2007 site?

Pages don't propagate from source to target variations


Hi there,

I have an existing site that I want to convert to a multi-lingual site.

I created an English source variation, next i create another variation (Portuguese) and ran the Create Hierarchies. I used the Manage Content and Structure web interface to move my sub-sites into the English source variation folder. All the sites and pages were created, although the default page is created with the default layout and not with the layout that i create, but all my content is missing in the Root Page Library of the Portuguese variation. Any of this pages is propagated..
Can i create publishing sites for this pages from my pages library in the variation home?

Or just with some workaround that i can do that?

Cumps, Nuno.

Data Flows in SSIS - Mapping Multiple source tables to Destination table **Newb question**

Hi I am new to SSIS and had a basic question. I have around 30+ tables in a db that needs to be migrated to a newer schema in the DB. The data flow task seems to be ideally suited for my requirement. My question is do I need to create 30+ different data flow tasks for this which will get executed one after the other or is there a better way to migrate large number of tables. Also how are referential constraints taken care of during such migration. Thanks and Regards, Ganesh Ranganathan
Ganesh Ranganathan
[Please mark the post as answer if it answers your question]

SSIS 2005 - Dynamically generate create table statement from source



I have a requirement where i had to migrate the table structure and data from ODBC database to SQL Server. I have dynamic source reader that uses sql query from variable to get the data and the next step is to get the create statement and create table and then to insert data. how can i get Create Statement the destination produces and use sql task to create the table and then insert the data using data flow task.

Please suggest



How to ensure all the fields from a source file is mapped to a destination file \ table


I am trying to verify if the SSIS package created by someone else has all the fields from source mapped to a destination table columns.

How to do that?

Also are there any best practices for testing for correctness of SSIS package.

OPENQUERY throws Error 7357 when the source SP uses temporary table.

Hello Everybody / Anybody,
Sorry but exiting problem!
The Problem: OPENQUERY throwing error [Error 7357]when the source SP uses temporary table.
Description : Need to validate data against master list. My combo on UI has a source Stored Proc(contains a temp table in it).
I'm importing data from Excel. Before import, I want to validate it against my master list values.
[say field Priority has master values "High, Medium,Low".] and in excel user has added 'ComplexHigh' under priority field]
In this case, my import validator StoredProc should not accept value 'ComplexHigh' as it is not present in my Priority master list]
I'm preparing a temp table tabName  containing o/p of SP, it works fine zakkas if my SP  usp_SelectData does not contain temp table. 
I think you got what the situation is!! Woh!

SSIS - load table from source text file with multiple record lengths


I have a text file that has mulitple records lengths that I need to load into a table.  The file has multiple record types and each record type detemines the length of the record.  Let me try to explain...the file has a balance line 1, multiple detail records, balance line 2, and a trailer record.

The first record is a balance line 1, which has a record length of 144.

Detail records with a record type of inv or crd, will have a record length of 147.  The remaining record types will have a length of 144.

The second to the last record is balance line 2, which has a a record length of 156.

And the trailer record, which has a record length of 162.

Here is a portion of my source file.  I have made each line bold where you can see the rec type.  The record starting with 9999 is the trailer.

077700001BL100000002010100120101008BALANCE FORWARD 0000000000000000001081175D0000000000{0000000000{0000000000{000001081175D00 0000000000{

077701000INV01953172010100120101008GROCERY - DRY 0000000000000000000073504I0000016678K0000000852G0000000000{000000057679D011N 0000093201E010

07779008099000000002010100820101008KC CASH REC. 0000000000000000001081175M0000000000{0000000000{0000000000{000001081175M00 00000000

SSIS : Insert, Update, Delete records from destination table by comparing records in source table.


Requirement: Inse

rt, Update, Delete records from destination table by comparing records in source table.

Update: if records exist in both the table compare them, and update value in destination table if value is different.

Insert: if record doesn't exist in destination table, add new record in destination table.

Delete: if record exist in destination table but not in source table, delete record from destination table.

----> I have created an SSIS package that does all this task but in order to perform delete operation, I have to store all records of destination table into a temporary table.
And since it's not the most efficient way, I have created another SSIS package by using Lookup & conditional Splits
method, now it works fine with inserting and updating information but I am not sure on how to handle removing record operation!

I would really appreciate if you could help me with this.

(In order to archive inserting and deleting records, I have referred following thread!
http://sqlblog.com/blogs/andy_leonard/archive/2007/07/09/ssis-design-pattern-incremental-loads.aspx )

"The data source control failed to execute the insert command." when inserting to sql table with tri



I have a data view web part that inserts a new record into a sql table. This works fine.

I have now needed to add a trigger to the sql table that sends an email when a new record is inserted (the code for which works fine) but now my insert fails with 'The data source control failed to execute the insert command.'.

If I disable the trigger the form works fine again.

Any ideas what could be causing this?


Can I have 3 different tables in a report? And, can I pass 3 temp table to SSRS as source for these

I'm using BI 2008 and sql 2005 and 2008.  Is there an example to show me?  Thanks.

SQL Select With Variable Table - Using with ODBC Data Source


Hello all,

Having difficulty with an idea for a SQL query today.  I am working with a data set that points to an ODBC data source.  The data source has several TRANSACTIONmmyyyy tables (mm - month, yyyy - year ), each holding data for a particular month, going back several years. Each table has identical fields and data types.

If I were to run the query below for just one month ( say I took out mmyyyy and plugged in a month and year, 052010 ) it will pull totals for the AccountNum I specify.


mmyyyy.AcctNum, SUM
mmyyyy.Amount ) As
 "Current Month"

TRANSACTION mmyyyy.Region = TOTALS.Region AND
TRANSACTION mmyyyy.Region

i cannot insert correct data to the target table




i am using oracle 10g database as source and target.

i am trying to load data from stage to dimension, but i cannot get correct data to the target table.




while loading this to target i am not getting correct data

example i am getting like this


the source and target datatype is also same.

can anyone give the solution for this




Validation when adding a new item and no target to source binding has yet taken place


Using: WPF (.NET 4) with Entity Framework on a SQLCE database.

In my app, the user can edit existing employees and create new employees. The same UI control is used to add and edit. In the case of editing an Employee, the DataContext for the control is the Employee object of my Entity Framework context. It is updated through binding, and the SaveChanges method is called on my EF to update the database. In the case of adding and Employee, the DataContext is a new Employee object, which will gets added to the EF and then saved. Simple.

I've been trying to hook-up validation to the control. I'm using WPF's ValidationRule objects for each field binding as required. When the field loses focus (as is the default action for a target to source binding), the validation rules are checked, which can incur an error. I can then check for the presence of these errors before allowing the employee to be saved.

But when adding a new Employee, if I go ahead and push the Save button without doing anything else, no validation takes place, becasue the bindings have never attempted to update the source! It just throws an exception as expected.

What can I do? I have ideas which would probably work, but I thought I should post here first in case I am missing something obvious! Is there a standard way to deal with t

Dimension where source DSV Table has multiple FKs to the same table


Hi All, if I have a dimension table "DimService" and it has 2 foreign keys to another dimension table "DimCustomer" on 2 different FK fields, lets say they are called SellTo and BillTo Customer.

If I wanted to show properties from both SellTo Customer and BillTo Customer it doesn't seem possible as there is no way to tell the dimension builder *which* relationship to go down for the magical join that it must be producing.

Is there any way to do this?

I have worked around it by creating named queries in the DSV (DimCustomerSellTo and DimCustomerBillTo) but of course this means that it needs to load the same data twice on processing which is sub-optimal.

Foreach Loop Container & File System Task to copy files from target server to source server


A one-off shot at using, SSIS, I appear to be missing something. 

From what I have read here, the Foreach Loop Container in which a File System Task has been placed is how one copies files contained in a source server folder to a folder on a target server.  I have defined Source and Destination pkg variables using UNC \\servername\aharedfolder and a FileName variable that is empty.  My impression is that this is suppose to iterate through and copies all files in the source folder to the target.  I only get the first file copied.

In the FLC Collection section  I am using the Foreach File Enumerator under which I have the Folder as \\servername\sharedfolder and files as *.* .  I am not using Expressions.  The Variables Mapping reference my FileName variable.

In the FST, I both path variables set to True and reference the destination and source variables described above.  Operation is Copy File.

I appears that I need to pass a list of the source folder file names to the FileName variable, rather than some built-in logic grabbing a files in the source folder.  Is a passed filename list what I am missing?

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend