SolvedPrivate

Update using SSIS

Posted on 2014-11-11
7
30 Views
Last Modified: 2016-02-10
Hi Experts, I have the following issue and I am struggling to come up with an idea on how to do it. It could be a simple solution but I am unable to figure it out.

I have a source and target table (I have attached the Table structure along with sample data) in two different instances and databases on sql server.

I have to update the StatusId in the Target Table  to 2 If the ChildItems are the same and the ParentItem Differs.

I dont have any Linked Servers, So I have to use SSIS. Is this possible using SSIS. If so how.

•      Source Server Instance:  SqlSrcInstance
•      Source Database:  SrcDatabase
•      Source Tables: dbo.CustSale (CustId, SaleId, ParentItem, ChildItem)

SrcData Example

CustId      SaleId      ParentItem      ChildItem
566845      1125447      ParentItemGroup1      Apple
567642      1127775      ParentItemGroup2      Blueberry
567643      1132347      ParentItemGroup1      Carrot
567641      1129767      ParentItemGroup3      Carrot
567641      1129768      ParentItemGroup2      Lettuce
567643      1132348      ParentItemGroup3      Lettuce
566929      1125448      ParentItemGroup1      Orange

•      Target Server Instance:  SqlTgtInstance
•      Target Database:  TgtDatabase
•      Target Table: dbo.SaleStatus (CustId, SaleId, StatusId)

Target Data Example:

CustId      SaleId      StatusId
566845      1125447      1
566929      1125448      1
567641      1129767      1
567641      1129768      1
567642      1127775      1
567643      1132347      1
567643      1132348      1

Thanks In AdvanceSource-Structure-Data.sqlTarget-Structure-Data.sql
0
Comment
Question by:AlfredBaker
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 40435672
If  you have update data from Server A to Server B you could do the following:

1) First you create a new "staging" table in Server B which has exactly the same structure as your source table from Server A. These table/s will be our staging/ temporary table/s

2) Then create a SSIS package, in the control flow drop a data flow task. In the data flow task Use an OLE DB Source and OLE DB Destination to copy data from Server A source table to Server B staging table (created at step 1 above)

3) Go back to the control flow and drop in an Execute SQL task. In the Execute SQL task write a simple T-SQL query to update data in the destination tables on Server B using the data from the staging tables in Server B.
0
 

Author Comment

by:AlfredBaker
ID: 40435706
Hi Icohan, Thanks for the comment. Is there any way that this can be done without creating a staging table  and just using transformations in SSIS.
0
 
LVL 40

Accepted Solution

by:
lcohan earned 250 total points
ID: 40435884
In my opinion not really because you need to run an UPDATE SQL Statement with some condition and the least that needs to be done would be to create a Linked server (on the target to the source table) so you can run on a single SSIS connection manager (target) an update from destination. Aside the complexity if UPDATE volumes are large the action may be extremely slow on distributed query across linked servers.
Aside that you could use the new SQL rowversion column to perform an incremental "staging" table population and further to identify only changed data into the staging for a quick 2nd,3rd,...etc. UPDATE.
If this is just a one timer then...it should not matter anyway and you could use the IMPORT/EXPORT wizard to get the data across from the source into a temp/staging table then use that one for the update.

Rowversion example below:
http://www.codeproject.com/Articles/698025/Rowversion-datatype-in-SQL-Server-Track-which-rows
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40436848
I have to update the StatusId in the Target Table  to 2 If the ChildItems are the same and the ParentItem Differs.
Sorry but I didn't get this part. You post some sample data. Can you identify which records this statement are referring to?
0
 

Author Comment

by:AlfredBaker
ID: 40437304
For the following 2 records from the source

567643      1132347      ParentItemGroup1      Carrot
567641      1129767      ParentItemGroup3      Carrot

I have to Update the StatusId in Target table to 2

And the Same way for

567641      1129768      ParentItemGroup2      Lettuce
567643      1132348      ParentItemGroup3      Lettuce
0
 
LVL 50

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
ID: 40437731
With a Linked Server would be more easy.
Will be more complex to find a solution with SSIS.
0
 

Expert Comment

by:Anshul Parmar
ID: 40593016
Try using query as a source instead of table -

SELECT *, 2 AS StatusId FROM [CustSale] b
JOIN (SELECT ChildItem
        FROM (SELECT ParentItem, ChildItem
                FROM [CustSale]
               GROUP BY ParentItem, ChildItem
             )n
       GROUP BY ChildItem
      HAVING COUNT(1)>1
     )a
ON a.ChildItem = b.ChildItem
0

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question