SolvedPrivate

Update using SSIS

Posted on 2014-11-11
7
31 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 51

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 51

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

627 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