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
AlfredBakerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
0
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.
0
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:
http://www.codeproject.com/Articles/698025/Rowversion-datatype-in-SQL-Server-Track-which-rows
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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?
0
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
With a Linked Server would be more easy.
Will be more complex to find a solution with SSIS.
0
Anshul ParmarSenior SSIS DeveloperCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.