<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by fediori
»
T-SQL insert using SSIS Data Pump
19 posts, Page 1 of 2
1
2
»»
T-SQL insert using SSIS Data Pump
Rate Topic
Display Mode
Topic Options
Author
Message
Federico Iori
Federico Iori
Posted Monday, February 04, 2013 11:34 PM
SSC Rookie
Group: General Forum Members
Last Login: Monday, April 15, 2013 1:33 AM
Points: 31,
Visits: 261
Comments posted to this topic are about the item
T-SQL insert using SSIS Data Pump
Post #1415582
nick.mcdermaid
nick.mcdermaid
Posted Tuesday, February 05, 2013 12:32 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Today @ 12:48 AM
Points: 160,
Visits: 430
I'd be interested in a performance comparison with a simple INSERT INTO / SELECT solution which does not use SSIS.
Post #1415608
Federico Iori
Federico Iori
Posted Tuesday, February 05, 2013 1:15 AM
SSC Rookie
Group: General Forum Members
Last Login: Monday, April 15, 2013 1:33 AM
Points: 31,
Visits: 261
You can try by yourself to compare .. as I have written, Insert Into is fully logged operations, requires more restrictive lock on target table and makes heavy use of transaction logs ..
If you insert into an empty table, you can use the hint with(tablock) and the insert will be, under certain conditions, minimally logged, but it puts an X lock on the table to insert into , while OleDb Insert requires only an IS lock and makes better use of transaction logs because commits inserted rows in batch..
Post #1415630
nick.mcdermaid
nick.mcdermaid
Posted Tuesday, February 05, 2013 2:40 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Today @ 12:48 AM
Points: 160,
Visits: 430
I probably won't have the opportunity to compare myself - thats why I read sites like this. But if I do have an insert performance issue, I might consider SSIS as a solution.
Perhaps you could also consider using the SQL Server destination instead of the OLE DB destination in appropriate cases for even mor performance.
Post #1415662
Federico Iori
Federico Iori
Posted Tuesday, February 05, 2013 2:43 AM
SSC Rookie
Group: General Forum Members
Last Login: Monday, April 15, 2013 1:33 AM
Points: 31,
Visits: 261
It depends how many rows and how many bytes per row you are going to insert.
If you start making DWH / ETL with SqlServer, you will probably face this kind of issues ..
Post #1415667
philcart
philcart
Posted Tuesday, February 05, 2013 4:46 AM
SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 3:27 PM
Points: 2,692,
Visits: 1,075
Sorry you lost me on a few fronts.
1) you reference another article stating, "The method SSIS Data Pump represents the most effective way to load large amount of data into a SQL Server database because the SSIS Data Pump makes optimal usage of the transaction logs, parallelism, locking, and pipelining. A more detailed explanation is in the referenced article.". Yet I see no such detailed explanation in the referenced article.
2) you call xp_cmdshell to execute your package. This won't be usable on systems that dis-allow usage of xp_cmdshell.
3) your xp_cmdshell calls are executing a bunch of commands you can do in SSIS with the FileSystem task, or at worst the Script Task.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
Australian SQL Server User Groups
-
My profile
Phills Philosophies
Murrumbeena Cricket Club
Post #1415735
Peter Pirker
Peter Pirker
Posted Tuesday, February 05, 2013 5:31 AM
SSC Rookie
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:28 AM
Points: 36,
Visits: 206
Excellent article.
When somebody gets around to writing the VB code in C#, could you pls. post it?
Post #1415762
Luke C
Luke C
Posted Tuesday, February 05, 2013 6:13 AM
SSC Veteran
Group: General Forum Members
Last Login: Friday, May 17, 2013 6:14 AM
Points: 238,
Visits: 900
Nice article!
Luke C
MCP, MCTS, MCITP - Database Administrator & Database Developer
Post #1415784
Federico Iori
Federico Iori
Posted Tuesday, February 05, 2013 9:08 AM
SSC Rookie
Group: General Forum Members
Last Login: Monday, April 15, 2013 1:33 AM
Points: 31,
Visits: 261
philcart (2/5/2013)
Sorry you lost me on a few fronts.
1) you reference another article stating, "The method SSIS Data Pump represents the most effective way to load large amount of data into a SQL Server database because the SSIS Data Pump makes optimal usage of the transaction logs, parallelism, locking, and pipelining. A more detailed explanation is in the referenced article.". Yet I see no such detailed explanation in the referenced article.
You are right , there is no such detailed explanation.
Purpose of my article was not explaining why SSIS Data Pump is the best method.
You can give a look to :
http://blog.staticvoid.co.nz/2012/8/17/mssql_and_large_insert_statements
and note that SSIS data pump is nothing else than a pipelined bulk copy.
2) you call xp_cmdshell to execute your package. This won't be usable on systems that dis-allow usage of xp_cmdshell.
How could I launch from T-SQL a SSIS package ? The only way I know is using xp_cmdshell.. purpose of the article was a T-SQL stored procedure able to launch a SSIS package, and this requires xp_cmdshell
3) your xp_cmdshell calls are executing a bunch of commands you can do in SSIS with the FileSystem task, or at worst the Script Task.
Yes, some of xp_cmdshell calls could be moved into BULK_TRANSFER_DATA.dtsx
Post #1415929
hdavidson88
hdavidson88
Posted Tuesday, February 05, 2013 9:14 AM
Forum Newbie
Group: General Forum Members
Last Login: 2 days ago @ 12:18 PM
Points: 4,
Visits: 43
Nice article and I picked up some information I could use in our business. We have different applications that need client data dispersed throughout various locations, which have table table and column names which are not the same as our source database. Our approach was to store the application name, source table name, destination table name, source connection string, destination connection string, source column name, destination column name into a single SQL table. We then call the dynamic package passing the application that we want to load up and the package loops through this mapping table dynamically creating the connections and table and column mappings. Which works fine for table loads, I was looking for information on how to use stored procedures for the data flow task, to update data on destination sources.
Post #1415932
« Prev Topic
|
Next Topic »
19 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
