asked
Reading from view
Hi! In an existing SSIS package, data is loaded from view to a table and it takes about 1 and half hour . Is it better to directly load the data to a temporary table and then rename it as destination table instead of using view. Any suggestions appreciated . Thanks a lot
are you using all of the columns in the view? what does the view do? lots of joins? calculations? nothing but a straight pass-through to a single underlying table? how many rows are you dealing with? The package takes 1 1/2 hours - how long does it take just to select everything you need from the view without sending it to a destination (replace your destination with a rowcount type object)? It's not necessarily "better" or "worse" across the board to load to a temporary table (I presume you mean a "permanent" table that is used for staging purposes). The "better" approach is usually what works best in your environment.
Just a quick note. You have asked 41 questions on the site and only voted 7 times. Please help us out and vote on the answers on your questions as well as mark solutions. That's how the site is supposed to work. Without your input after you get help, it just doesn't function.
Here is a questons: what destination are you loading data into, Access, Excel, Text document,etc. It will be a lot easier if we know where the data is going. Each of these destination types will have thier pros and cons.
Thanks Everyone , I used an Execute SQl task and loading directly to the destination table it reduced the time by 30 min .It involves lot of complex calculations and about 70 million records. Appreciate all your help thanks
try loading data using instead of trigger(i know the word trigger make people scary)..performance was better
http://technet.microsoft.com/en-us/library/ms175089(v=sql.105).aspx
17 People are following this question.
