October 19, 2017 at 10:24 am
Morning All,
I have an SSIS package deployed on Server C.
The Data resides on Server A and the Destination is on Server B.
A SQL Server Source (Server A) is created in the package and a minor transformation occurs and the data is pumped into a destination connection (table on Server B)
My question is, does the data flow through Server C. Or is SSIS intelligent enough to know Server C is just hosting the definition of the package and that data is going from A to B?
Cheers
Alex
October 19, 2017 at 10:59 am
I could be wrong, but I am fairly certain that the data would hit server C.
Server C would be doing all of the data transforms which SSIS is usually used for.
So Server C would pull the data from A into memory then after that completes it would look at the next step in the SSIS package. If the next step is to send the data to server B, then it will just pass the data along. But if any other steps are being done (sorting, adding/removing columns, data transforms) it would be performed on server C before passing it along to server B.
My reason for thinking this is that we have a few SSIS packages that just do data transfers from server A to server B through server C (server C with SSIS) and it does large table loads and I see noticable memory spikes during the load times on server C even though it is just grabbing the data from A and putting it into B.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
October 19, 2017 at 11:01 am
Unless your package is executing a sql statement using linked servers against either A or B with no data flow present then yes it is flowing from A => C => B
If both are SQL Server servers then make sure you tune the package correctly in terms of buffer size/num rows on dataflow, fast load, packet size on connection string as to improve through output.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply