October 18, 2011 at 9:00 am
Hello all,
I was reading the previous posts and have a similar issue. I am new to SSIS and SQL Server in general.
I created a basic package where my Control Form consists of an "Execute SQL Task" and "Data Flow Task". My SQL task consist of
CREATE TABLE "PLUGSSIS" (
"JOB_NAME" VARCHAR2(50),
"JOB_ID" VARCHAR2(22),
"ACCT_NO" VARCHAR2(50)
)
My Data Flow consists of Source Connection (Excel Spreadsheet), Data Conversion, and OLE DB Destination.
Simply I am just pushing data over a WAN from an excel spreadsheet into an oracle server. I was successful at doing this but it took about 30 minutes to upload 2000 rows to the server. Any ideas as to how I can increase the performance?
FYI - I am currently using SQL Server 2008 R2, SQL Management Studio and Visual Studio 2008
Any information is greatly appreciated.
Thanks.
Fab
October 18, 2011 at 10:30 am
So, 30 minutes for 2000 rows, yeah that performance sucks. I am assuming you are running this at your desk in BIDS?
Are both the file server that holds the spreadsheet and the oracle server on the WAN or is it just you. I'm trying to get a handle on the layout.
Spreadsheet on server <--> WAN <--> BIDS <--> WAN <--> Oracle Server
or
Spreadsheet on server <--> WAN <--> BIDS <--> LAN <--> Oracle Server
or
Spreadsheet on server <--> LAN <--> BIDS <--> WAN <--> Oracle Server
or what.
If you look at it in BIDS how long does it take before the number comes up after source turns yellow? right away? I'm trying to figure out where the problem is, source or destination. Also did you chose the fast load option on the destination?
CEIWI
October 18, 2011 at 11:17 am
Hello,
I am currently running BIDS from my desk and the spreadsheet is stored locally on my c drive as well.
The source seems to be fine since initially when executing the package it turns green then displaying 2000 rows below the Source Connection box in the data flow area, Data Conversion box is set to yellow and below shows 1863 records and the destination hangs on yellow until all data is uploaded.
Spreadsheet on local machine along with BIDS (same machine) <--> WAN <--> Oracle Server
Thanks for your response.
October 18, 2011 at 11:18 am
Ok, so source isn't the problem. On the destination side do you have fast-load selected? If not then it is doing them one row at a time which will always take longer. Also, how fast is your WAN link?
CEWII
October 18, 2011 at 11:20 am
I did not choose the fast load option since I was not aware of that.
I am looking into that now.
October 18, 2011 at 11:30 am
On the fast load option where on the destination side do I enable that feature? Is that under Properties / Custom Properties / Access Mode / OpenRowset using FastLoad
I am probably going to have to speak with networking on the WAN link.
All this information is good stuff, I appreciate it.
Thanks.
Fab
October 18, 2011 at 11:34 am
yw
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply