Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

ssis data flow task taking an extremely long time . Expand / Collapse
Author
Message
Posted Monday, April 22, 2013 2:03 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 5:22 PM
Points: 58, Visits: 197
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 :

1)
select Id, XML1, XML2, Time1, Time2 where Time > 'XXXX' and Time2 < 'XXXX'

Converting Varchar to xml
2)
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 ?
Post #1445157
Posted Monday, April 22, 2013 8:03 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:15 PM
Points: 35,399, Visits: 31,959
Are both databases on the same server?

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1445239
Posted Tuesday, April 23, 2013 1:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:11 AM
Points: 5,189, Visits: 12,055
Taking SSIS out of the equation for a moment, do the queries run quickly in SSMS?

You need to do some detective work to understand exactly where the bottleneck is.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1445293
Posted Tuesday, April 23, 2013 7:22 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 5:22 PM
Points: 58, Visits: 197
@Jeff : The databases are on two different servers.

@Phil : Thank you ! . I will check the queries.
Post #1445408
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse