March 15, 2012 at 4:41 pm
Hi Folks
I am trying to design a SSIS 2008 package which will import data from Excel-2007 sheet into SQL Server table.
Initially i thought that i will use DataFlow Task which will have
-Excel-Source ( .XLSX File is located on Network-Shared Folder )
-Derived Column Transformation ( Because i need to add 1 new column )
-OLE-DB Destination.
But i see that using this way, it take more than 1 Hr to load approx 15,000 Rows.
So i can understand that this is not best way to design a package.
Now I am trying to check with Bulk Insert & OPENROWSET(BULK) commands.
Q-1
So can you guys suggest some better way...!!!
Q-2
I am creating package locally, on my work-station.
Is this happening because SSIS Package runs from my local Machine, loads/brings data from Shared-Network folder to my local machine & then does remaining work...!!!
Any link/article would be appreciated...!!!
Thanks in advance
DEVSQL
March 15, 2012 at 5:27 pm
Hmmmm......
15K rows per hour is WAY too slow. Something is wrong somewhere. What's the definition for the target table? Does it have a trigger of any kind? If you run the package in debug mode, is the data slow to come out of the spreadsheet or slow to go into the target table?
You should definitely be getting better throughput than that on any decent network. Is it faster if you bring the spreadsheet to your desktop, or run the package from the SQL Server?
March 16, 2012 at 1:42 am
My guess is that you have selected the Table or View option in the OLE DB Destination.
Change this to Table or View - Fast Load.
And be amazed 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply