where is my commit size in my pkg
-
hi, while trying to give our operations guys as much info as possible about a separate ssis issue /post we have with losing connections locally and/or run times of 60 x normal when vpn'd, i looked for my commit size under vs 2022 with ssis data tool 16.0.5397.1. i thought it was supposed to show in my oledb destination component but i dont see it in the editor or advanced editor or properties of that component or properties of the connector. my table access mode is "table or view". what am i doing wrong?
-
Right click the dataflow task itself and view properties. Have you tried using table or view -fast load? If you can achieve minimally logged inserts they are typically much faster.
-
thx ed are you talking about these? so every source to dest occurrence in the dft shares in this total or has its own pair of these same limits. i could have sworn in past versions of ssis these attributes were at the destination level and tagged as a commit count. buffer doesnt sound like commit but i have to trust you.
-
not those settings (which should be changed in most occasions - just don't set it to automatic on newer version of SSIS)
-
One thing to note about things being slower when run over VPN in SSIS, slower than where? Slower than the exact same workstation (laptop/desktop) being on site OR slower than the SSIS server?
The reason I ask this is if it is slower than the exact same workstation, then the slowness is caused by either the VPN or the internet provider speeds. IF it is slower than a different machine (ie server), it could be memory issues and things are paging to disk. Being 60 times slower makes me think it is more likely to be paging to disk than due to bandwidth issues, but that's just a guess. Might not hurt to grab a bandwidth monitor tool (I use BitMeter which is freeware, but also old and not really supported) to see if your bandwidth is being maxed out while it runs or if bandwidth spikes at the start then slows down as it fills up your memory and needs to page to disk. I'd also check your system resources. If I remember right, SSIS uses 32-bit mode in Visual Studio which has a 4 GB limit on memory.
Not saying the VPN/internet speeds are NOT the problem, just with that big of a drop in performance, I would also check things like memory utilization.
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. -
thx Brian. Unfortunately you are seeing a mention about the speed issue out of context. I was just trying to find COMMIT size here like i believe we used to see and set in previous versions of ssis by right clicking the oledb dest. i mentioned the speed issue only because i wanted to document the commit size for the speed issue and didnt want to pile questions on at that link.
i'll post here a reference to the speed post in a few moments where you can see that the answer to "compared to what?" is "compared to itself" where in the last 10 days or so it seems any tool i use with a transport layer has gone nuts.
So for example a simple pkg that used to run in a few seconds while i was vpn'd now runs 60 times longer than it used to while i am vpn'd. but at least it finishes when vpn'd. Inside the firewall (ie at the office) the same pkg drops its connection 100% of the time within seconds.
that post is a long post but i felt it was important to collect as much info as possible there before involving our network and operations guys.
here is that link https://www.sqlservercentral.com/forums/topic/error-in-both-ssis-and-ssms-something-about-losing-connections-never-had-thi
-
thx frederico. so that link basically says its only a fast load option. bad memory on my part.
- stan wrote:
thx Brian. Unfortunately you are seeing a mention about the speed issue out of context. I was just trying to find COMMIT size here like i believe we used to see and set in previous versions of ssis by right clicking the oledb dest. i mentioned the speed issue only because i wanted to document the commit size for the speed issue and didnt want to pile questions on at that link.
i'll post here a reference to the speed post in a few moments where you can see that the answer to "compared to what?" is "compared to itself" where in the last 10 days or so it seems any tool i use with a transport layer has gone nuts.
So for example a simple pkg that used to run in a few seconds while i was vpn'd now runs 60 times longer than it used to while i am vpn'd. but at least it finishes when vpn'd. Inside the firewall (ie at the office) the same pkg drops its connection 100% of the time within seconds.
that post is a long post but i felt it was important to collect as much info as possible there before involving our network and operations guys.
here is that link https://www.sqlservercentral.com/forums/topic/error-in-both-ssis-and-ssms-something-about-losing-connections-never-had-thi
To me then that sounds like a QoS setting or a bandwidth issue. SOMETHING is throttling your bandwidth over the VPN. Your work ISP has a limit on bandwidth as you can't have unlimited bandwidth. So if you have a lot of people working from home, then you are going to have to either throttle the users (QoS) OR you leave it un-throttled and you get periods of time where everyone is slow (someone or multiple users using all the bandwidth) and periods of time when it is fast (such as when most users are sleeping).
But sorry - I was not trying to divert/hijack the topic, I was just trying to make sure the correct thing was being looked at.
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. -
thx Brian. appreciated and i didnt feel like you were hijacking.
the curious thing is that its affecting only myself and possibly one other person in her SAP work.
and we work in an IT dept with about 100 people.
the vpn behavior (60 x longer) always occurs for me only (as of late) no matter what time of day and when half of us in another state / time zone arent even at work yet. and the connection dropping inside the firewall always occurs for me only (as of late) no matter what time of day.
one of our security guys is wondering if its a policy issue.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply