Hey All:
I was totally confused.
When designing the SSIS dataflow part, firstly , i tried SQL Server Destination because my target server is a sql server.
then execute the task with failure.
Then i tried to use OLE DB DESTINATION instead of SQL Server Destination.
This Dataflow worked.
i can not figour out why.
By the way , i used the connection is OLE DB.And i choosed OLE DB source as the datasource cuz i can not find SQL server datasource.
Who can tell me some reasons for this?
Have you searched Books On-Line?
The SQL Server destination requires that you have SQL Server running on the same machine that you are executing the package on. Also, just to have SQL Server running on the machine isn't enough; it has to be your destination. The SQL Server destination is an in-memory operation, essentially.
The OLE DB Destination isn't bound to those constraints.
|||Using the OLE-DB destination is fine, and is probably the most common choice. Obviously you will then use an OLE-DB connection, selecting the OLE-DB provider for SQL Server. That is all good, don't worry.
You have however and advanced choice with the SQL Server Destination. It has options that make it the faster than OLE-DB, and one way they do this is by using the shared memory connection method. As you might guess from the name it means that the SSIS package pipeline and the SQL Server must be on the same machine. This makes for hard work when you wish to develop against a server running on a different machine to the development tools. For this reason I generally avoid it, unless I am really concerned with insert performance, and more often than not the bottle neck is elsewhere so the SQL Server Destination is over kill anyway.
|||Many thanks
But the new issue is that dam slow~~
60,000 rows from a static table which server loactes in Germany to the US server costs over 30 minutes.
even worse than <select .. openquery()>
why?
|||Solved!
i used a txt flatfile as a buffer intermedia.
|||If the "buffer" is between two SSIS tasks or packages, then use a raw file. Raw files are faster to read and write than txt files, as they are the pipeline engine's buffer structures from memory straight onto disk, without any translation or interpretation. See teh Raw File Source & Destination.|||DarrenSQLIS :
Rock~
No comments:
Post a Comment