June 5, 2012 at 4:13 am
HI All,
What is the main difference between these two?
if i want to do only export data (do not require any transformation) from/to excel files then we can use other methods OPENREOWSET directly in SQL Server than SSIS.
Which is better option?
🙂
June 5, 2012 at 3:06 pm
They're two ways to do the same task. SSIS OLE DB Destination with Fast load uses OPENROWSET to leverage the BULK INSERT interface. OPENROWSET when used from T-SQL with a format file also leverages the BULK INSERT interface.
The main difference in terms of the two methods is that when using OPENROWSET through T-SQL you're having the database engine reach out to the file system to parse the file and feed it into the BULK INSERT interface. When using SSIS, the SSIS package does the file system access and feeds the data into the BULK INSERT interface.
The nice thing about using SSIS to do this work is that you can offload those cycles to a different machine, one that is not involved in doing other database operations related to serving data to applications. There are nice things about using OPENROWSET from T-SQL as well. I prefer to do this kind of work in SSIS.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply