May 25, 2017 at 3:39 pm
Sergiy - Thursday, May 25, 2017 3:14 PMThe main reason my company went with SSIS is that my boss doesn't like linked servers.
Use OPENQUERY, OPENROWSET, etc.Same connection using the same driver.Except - it's only 1 connection from one SQL Server instance to another, instead of 2 - from SSIS to each of instances.And you don't need an extra server (SSIS) eating into your memory and other resources.
OPENQUERY would still use a linked server would it not?
OPENROWSET would work without using SSIS... May have to poke at that and see what we can use that for instead. Performance is a mild concern as we have some SSIS packages that run hourly that we use to pull reporting data across.
I think for anything where we are doing a direct data pull with no manipulation, OPENROWSET should work nicely. We do have some things though that do some data manipulation where I'd need to re-work the logic to get it from 100's of SSIS steps down to a single TSQL query. But doing things like that helps understand the logic and can speed things up as some of the SSIS stuff requires the data to be sorted which is a slow operation... especially if you need to sort it multiple times due to the transforms being applied. Some things happen in parallel as well which would be more difficult to do in TSQL.
One of our larger SSIS packages is over 4 MB in size. Most are in the KB range of size and under 100KB, but we have a few that are in the MB size.
We do like having a centralized server for all of our ETL related SQL jobs (ie non-maintenance and backup/restore) where possible. This makes maintaining and monitoring our ETL process a lot more simple. Could use 2 sets of OPENROWSET (one ot pull the data into a temp table and one to write it to the destination) and do it all in TSQL, but this feels like we are missing the benefit of the single connection and SSIS would make that easier to maintain long term.
I also like the reporting you can do for SSIS packages. It is slow, but it shows all the information I care about.
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.
Viewing post 31 (of 30 total)
You must be logged in to reply to this topic. Login to reply