Hello everyone .
I have a log table that I am planning to import from my Transaction Database to my reporting Database.
The columns of the table are as follows:
Id,Varchar1 , Varchar2, Time1, Time2
XML structures are stored in Varchar1 & varchar2
I am using a SSIS package and a data flow task with an OLEDB source and destination.
There are close to 600000 records in the table and its taking an extremely long time to transfer.
The queries that I tried so far in my OLEDB source are :
select Id, XML1, XML2, Time1, Time2 where Time > 'XXXX' and Time2 < 'XXXX'Converting Varchar to xml
select Id, CONVERT(XML,CONVERT(NVARCHAR(max),Varchar1, 0)), CONVERT(XML,CONVERT(NVARCHAR(max),Varchar2, 0))
, Time1, Time2 where Time > 'XXXX' and Time2 < 'XXXX'
Both the queries take me an extremely long time.
Does anyone have a better solution/approach to achieve my task ?