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.