Go Premium for a chance to win a PS4. Enter to Win

  • Status: Solved
  • Priority: Medium
  • Security: Private
  • Views: 33
  • Last Modified:

Update using SSIS

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
  • 2
  • 2
  • 2
  • +1
2 Solutions
lcohanDatabase AnalystCommented:
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.
AlfredBakerAuthor Commented:
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.
lcohanDatabase AnalystCommented:
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:
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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?
AlfredBakerAuthor Commented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
With a Linked Server would be more easy.
Will be more complex to find a solution with SSIS.
Anshul ParmarCommented:
Try using query as a source instead of table -

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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now