Transferring a large data set from server to server or even from database to another on the same server or even from table to table in the same database from SQL server is very big problem concerning time, efficiency, and memory and space together.
I was facing this problem in transferring large data set, contains 1.5 million records per table and each table set is a query from source tables. so the output is transferring query results to a tables. It sounds simple but it is not!
when doing it by SQL import wizard it will take one query per time!!!! how could they think i will need only this!
when trying to do it by SSIS this where i went to hell.... although SSIS has some benefits at least outlining the task flow of the work, but transferring large amount of data is much bigger than it.
my server specs are:
Core 2 due intel processor
6 G.B Memory
win 2008 64 bit
60 GB disk space
It sound OK for transferring 1.6 million records from a query, but it took nearly 1 hour to do so.
Sure I added indices and all the tuning it should take, but with no big improvement.
The best solution I found is: "Bulk insert"...
but how could i dump 1.5 million to a text file quickly as SSIS also fails doing that. the solution to this problem was bcp utility. "bulk copy" how could not Microsoft integrate it into SSIS or in the import wizard. it is like hiding a treasure.
bcp is brilliant in that, minimizing the time into seconds instead of hours in both dump in files and import into table. the only concern about that was to have a good HDD space to dump files on and then deletes it.
I am new to Silverlight, but I tried the Pivot Viewer to display lists from Sharepoint. the task seems fine unless:
make a dynamic list
problem with images
the first one I got this article which is quite powerful . so it solved me Part one.
the second one I tried to understand why my images are not displayed, but when importing some images from the internet it works. I got an answer that these images that works are DZI (Deep Zoom Image). and there is a DZI Composer!! rocks... Hell no. the composer outputs a bunch of files to work for the concept of DZI.What I only need is one picture! Is this Hard :(
*DZI for simplicity, is making the image in layers so that it loads fast, and when zooming we can get the more detailed layers.
I am trying to google around and there should be any luck.
Pivot Viewer now is good but it should be customeized. they will, but in next release....
SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
this error message has many sources:
connection is used with user id, and password and not saved
I tried to google and found most of the replies are for Oracle and Excel, and sometimes the password. while my case does not include any !!!!!
so I found the delayvalidation and it seemed works but nothing happened. I tried to make the things easier as My package runs in 4 hours and deals with more than 30Million * 200,000 * 1000 records with other tables that compared with the above is look-ups.
what make me confused that it sometimes runs ok, but some other times gives me an error at anytime!!!!
Then in any Normal Scenario [according to Microsoft], this will solve it unless you have the [Large Dimension Problem] then it will not affect anything.
the Large Dimension Problem starts when you have at least one dimension with 170,000 records and more. In my case I have 220,000 records So I guess there are no solution.
I read a lot about migrating from SSAS 2005 to SSAS 2008, and also from 32bit to 64bit. but the reviews still as it. it should enhance the memory usage, but the same processing.
another thing that might be interesting, when I browse from Excel 2007 @ the server things are mmm not that bad, but doing the same from a different computer, then the disaster happens when I get to the large dimension.
what Microsoft expect to have small dimensions for making them work fine !!!!!