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 @ 7:27 AM
Points: 35,769, Visits: 32,437
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 @ 2:15 AM
Points: 5,317, Visits: 12,354
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.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
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