Need to transform data between tables in two different servers

Hi Team,

Problem Statement:-

I am having a table name A in QA server with schema
<pre lang="SQL">CREATE TABLE [dbo].[A]([QuoteID] [int] NOT NULL,[AgencyID] [int] NULL,Status] [nvarchar](30) NULL,  [LOB] [nvarchar](30) NULL,  [ClaimStatus] [nvarchar](30) NULL,[TPA] [nvarchar](255) NULL,   [ClaimNumber] [nvarchar](255) NULL,[XMLData] [ntext] NULL)</pre>

Table named B in DEV Server.

<pre lang="SQL">CREATE TABLE [dbo].[B]([QuoteID] [int] NOT NULL,[AgencyID] [int] NULL,Status] [nvarchar](30) NULL,  [LOB] [nvarchar](30) NULL,[XMLData] [ntext] NULL )</pre>

If you see the schema differnece, in Table B we are not having Claimstatus, TPA, Claimnumber.

When we transform data from A to B,Need these 3 column data should be append into XMLdata  column as xml nodes. likes
<pre lang="xml"><Claimnumber></Claimnumber>
<TPA></TPA>
<ClaimStatus></ClaimStatus></pre>
 in table B.

For Example Table A
quoteid agencyid  status    lob    claimstaus   TPA       Claimnumber        xmldata
23857   61346     InForce   Claim   Closed      McLarens  15.009330.00-B    <pre lang="xml"><session id="23857">
  <properties dateModified="2011-01-18" manuscript="Carrier_Claims_Vacant_Property_1_0_0_0" engineVersion="2.0.0" cultureCode="en-US" cultureName="United States [english]" context="Underwriter" caption="Vacant Claim">
    <userName>jegarcia</userName>
  </properties>
</session></pre>

 i need this data in Table B as like

quoteid agencyid  status    lob           xmldata
23857   61346     InForce   Claim      <pre lang="xml"><session id="23857">
  <properties dateModified="2011-01-18" manuscript="Carrier_Claims_Vacant_Property_1_0_0_0" engineVersion="2.0.0" cultureCode="en-US" cultureName="United States [english]" context="Underwriter" caption="Vacant Claim">
    <userName>jegarcia</userName>
  </properties>
<Claimnumber>15.009330.00-B </Claimnumber>
<TPA>McLarens  </TPA>
<ClaimStatus>Closed</ClaimStatus>
</session></pre>
<pre lang="SQL"></pre>


I am having morethan 300,000 records in table A so need to do same manipulation and need to send to Table B.


I created one SSIS package which is taking 25 mins for first 5000 rows in Table A for 300,000 it may take more than one day which is too long.

My SSIS package

1)created on tempdata table in QA.
2)In BIDS, Added Sequence container to Control flow
3) Added Data flow task where I am inserting first 5000 rows from Table A
4) Added "Execute SQL task", where in sql statement <pre lang="SQL">Select quoteid from tempdata with (nolock)</pre> and created selected result set as " Full result set"
created a variable QID with datatype as "object" and added it in "Result set" link where result name is "0".
5) Craeted variable QuoteId with datatype as "int"
Added  ForEachLoop container, selected
In Collection
Enumerator : ForEach Add Enumerator
ADO object source variable : User:Qid
Variable Mapping: Quoteid, index=0

6) Added "Execute SQL task" in ForEachLoop container
which will execute SP in Sql statement <pre lang="SQL">EXEC [dbo].[ModifyQuoteXml] ?</pre>
where Parameter Mapping i am passing varaiable User::Quoteid

<pre lang="SQL">
USE [QA]
GO
/****** Object:  StoredProcedure [dbo].[ModifyQuoteXml]    Script Date: 09/02/2014 16:07:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ModifyQuoteXml] @QuoteId int=0
AS
BEGIN
    SET NOCOUNT OFF;

declare @CN varchar(25),@CS varchar(25), @TPA varchar(25)


select @cn=claimnumber,@cs=claimstatus,@tpa=tpa from [A] with (nolock) where quoteid=@quoteid


update tempdata
Set XmlData.modify('insert <ClaimNumber>{sql:variable("@cn")}</ClaimNumber> as last into(session)[1]')
from tempdata with (nolock) WHERE quoteid=@quoteid;


update tempdata
Set XmlData.modify('insert <Claimstatus>{sql:variable("@Cs")}</Claimstatus> as last into(session)[1]')
from tempdata with (nolock) WHERE quoteid=@quoteid;



update tempdata
Set XmlData.modify('insert <TPA>{sql:variable("@tpa")}</TPA> as last into(session)[1]')
from tempdata with (nolock) WHERE quoteid=@quoteid;


    SELECT @quoteid;
END

</pre>


which will insert xml nodes in my tempdate table.

7) Created new data flow  and connected with sequence container.
Which will simply transfer data between teampdata to Table B in Dev.


Please let me know is there any way to achive this functionality with minimum excution time


Thanks in advance.