SolvedPrivate

Update using SSIS

Posted on 2014-11-11
7
26 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
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 39

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 39

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 46

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 46

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now