July 16, 2008 at 2:18 pm
I am running an SSIS package on my laptop - which connects to a server
I am doing a data import - across different databases (on different servers)
I have been monitoring the destination database file size - and I dont see it increasing as the data is being inserted.... the database size increases only after the ssis package executes successfully.
Where is the data being stored duting package execution? I am curious to know what resorces are used up in ssis package execution - my laptop? or the server?
If answer is the server, then, if there is a data conversion task for the import, where does that happen? laptop? server?
July 16, 2008 at 2:27 pm
Almost everything in SSIS takes place in memory on the machine the package is running on. If you are running your package as a single transaction then the database is not going to grow until the transaction is complete.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 16, 2008 at 11:09 pm
great. That is what I kinda suspected.... so if I am going to transfer 200GB of data and run the package from my laptop (which has 50GB available) it would fail?
Is there a way to not do this as a transaction - just plain direct insert? are those options available in ssis? I know bcp could specify the number of records to process at a time... is that possible with ssis (which I think uses bulk insert)?
Thank you very much for your reply.
July 17, 2008 at 7:04 am
It won't fail as SSIS does things on a row by row basis so you do not need to have 200GB of storage on the machine where the package is running. The package is inserting the data, writing to the destination database transaction log, then the database is writing the inserts to the database file. You should see your transaction log growing during the process.
I am sure that there are others who can better explain exactly how SSIS works, but the key thing you need to know is that the machine running the package basically is just using RAM and CPU. If there are any lookups then the package may use tempdb on the server that the lookup is from.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 17, 2008 at 11:04 pm
I have been monitoring the transaction log... it hasn't grown past 500mb. I am inserting 9million rows from a table with 250 columns! Database size grows by 10-12GB. Maybe it's putting it in the server's tempdb but I cant think of a way to check that (Server has a 16 processor - so I set it up with 16 tempdb files of 2GB each)
Interesting though. Thank you for your reply. If I ever find out for sure, I will post back here
July 21, 2008 at 2:08 am
what is the major difference between dts and ssis packages??
August 3, 2008 at 10:25 pm
the major difference is that they threw out DTS and wrote SSIS from the ground up. It's completely different.
Jamie Thompson has written an excellent blog on this which may help... http://www.sqlservercentral.com/articles/SQL+Server+2005+-+SSIS/thenewetlparadigm/1719/
Kristen Hodges
b. http://bi-tch.blogspot.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply