February 13, 2017 at 1:28 pm
Hi ...been awhile since I last signed in... new job back to my favorite db platform SQL Server 🙂
I'm stuck a dilema and wanted to lean on wisdom of experienced DBAs/Devs on this site. (It's a great community on this website)
I've now the defacto DBA/IT guy for our Servers/DBs. The organization has been rebooted every 2-3 years and new people come in and out... it's a mess... however some great ideas on DB / Dev side but nothing fully implemented.
I'm trying to work towards consistency and a long term vision...
What aspect are guidelines when to use SSIS vs SPROC?
(Do you have a centralize SSIS/ETL server? )
- I see staff here embed a few sprocs into SSIS -
The sproc does no ETL really.. just execution of small sets of updates or insert small rows of data. (nothing large of vast) ..less than 1000 records
..."seems" like a candidate for plain old SQL agent job with multiple job steps (maybe) .. however if devs use SSIS they can wrap the package in a transaction.. Developers may not do that with a sproc/SQL script.. (unless policed/audited thoroughly)
- Then I see staff use SSIS with ETL for it's true purpose pulling a ton of data which seems to be right approach in those circumstances.
However SSIS allows for centralizing ETL/job processing and management much easier perhaps.
What have other people done with Guidelines or (if you have some) dictated or used to help guide teams in the right direction. I know there's not a one size fits all, but interested in more consistency.
Thank you for any thoughts/feedback
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
February 13, 2017 at 2:22 pm
For bulk importing data, I'd use SSIS over a stored procedure. If a stored procedure call is one smaller step in a broader ETL process, then it should be called from a T-SQL task within the SSIS package. If the stored procedure is called standalone at regularly scheduled intervals, outside the broader ETL process, then I would schedule it as a SQLAgent job. I wouldn't wrap an SSIS package around a process that involves nothing more than a single stored procedure call.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 13, 2017 at 2:30 pm
There's nothing wrong with using SPROCS in SSIS, but you're correct if you have SSIS packages that aren't being used for anything more than a shell to schedule running a SPROC that's likely not the right approach.
February 13, 2017 at 9:59 pm
ZZartin - Monday, February 13, 2017 2:30 PMThere's nothing wrong with using SPROCS in SSIS, but you're correct if you have SSIS packages that aren't being used for anything more than a shell to schedule running a SPROC that's likely not the right approach.
I agree with this - mostly. If the packages are chained or otherwise interconnected in some way then keeping everything in SSIS could be correct.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 14, 2017 at 8:57 am
Eric M Russell - Monday, February 13, 2017 2:22 PMFor bulk importing data, I'd use SSIS over a stored procedure. If a stored procedure call is one smaller step in a broader ETL process, then it should be called from a T-SQL task within the SSIS package. If the stored procedure is called standalone at regularly scheduled intervals, outside the broader ETL process, then I would schedule it as a SQLAgent job. I wouldn't wrap an SSIS package around a process that involves nothing more than a single stored procedure call.
Thanks for the message and feedback, yes that's what I feel too:
I do agree a small sproc with no major ETL processing or any thing but just an update shouldn't be in an SSIS.
I am wondering is there some benefit that I'm missing especially in SQL 2012 with new SSIS environment configurations. (I don't have experience with SSIS db /env setups with 2012 SQL .. not really a SSIS guy... though can build/dabble making packages)
Example someone could configure a prod/stage/dev environment to run a 'job/package' without creating multiple SQL Agent Jobs.
... would that be better than creating 3 packages on different hosts?
Or would encapsulating into a SSIS package take advantage of building a centralized ETL / job processing server?
(there are disadvantages of single point of failure... but assumption there's a fail over/redundnacy)
thanks!🙂
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
February 14, 2017 at 9:12 am
sqlsurfing - Tuesday, February 14, 2017 8:57 AMEric M Russell - Monday, February 13, 2017 2:22 PMFor bulk importing data, I'd use SSIS over a stored procedure. If a stored procedure call is one smaller step in a broader ETL process, then it should be called from a T-SQL task within the SSIS package. If the stored procedure is called standalone at regularly scheduled intervals, outside the broader ETL process, then I would schedule it as a SQLAgent job. I wouldn't wrap an SSIS package around a process that involves nothing more than a single stored procedure call.Thanks for the message and feedback, yes that's what I feel too:
I do agree a small sproc with no major ETL processing or any thing but just an update shouldn't be in an SSIS.I am wondering is there some benefit that I'm missing especially in SQL 2012 with new SSIS environment configurations. (I don't have experience with SSIS db /env setups with 2012 SQL .. not really a SSIS guy... though can build/dabble making packages)
Example someone could configure a prod/stage/dev environment to run a 'job/package' without creating multiple SQL Agent Jobs.
... would that be better than creating 3 packages on different hosts?Or would encapsulating into a SSIS package take advantage of building a centralized ETL / job processing server?
(there are disadvantages of single point of failure... but assumption there's a fail over/redundnacy)thanks!🙂
Yes, you can design an SSIS package with Connection Manager properties configured dynamically using project parameters, so you can swap out the server name or set a parameter called ENV to "DEV", "QA" or "PROD". That is useful when you have a package that connects to other remote servers. However, if the package only references local databases, you can just set ServerName to "(local)", and it will run in any environment without modification. If you have a stored procedure that references remote tables using linked server connections, then that's a good candidate for migrating to SSIS.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 15, 2017 at 11:56 am
Eric M Russell - Tuesday, February 14, 2017 9:12 AMsqlsurfing - Tuesday, February 14, 2017 8:57 AMEric M Russell - Monday, February 13, 2017 2:22 PMFor bulk importing data, I'd use SSIS over a stored procedure. If a stored procedure call is one smaller step in a broader ETL process, then it should be called from a T-SQL task within the SSIS package. If the stored procedure is called standalone at regularly scheduled intervals, outside the broader ETL process, then I would schedule it as a SQLAgent job. I wouldn't wrap an SSIS package around a process that involves nothing more than a single stored procedure call.Thanks for the message and feedback, yes that's what I feel too:
I do agree a small sproc with no major ETL processing or any thing but just an update shouldn't be in an SSIS.I am wondering is there some benefit that I'm missing especially in SQL 2012 with new SSIS environment configurations. (I don't have experience with SSIS db /env setups with 2012 SQL .. not really a SSIS guy... though can build/dabble making packages)
Example someone could configure a prod/stage/dev environment to run a 'job/package' without creating multiple SQL Agent Jobs.
... would that be better than creating 3 packages on different hosts?Or would encapsulating into a SSIS package take advantage of building a centralized ETL / job processing server?
(there are disadvantages of single point of failure... but assumption there's a fail over/redundnacy)thanks!🙂
Yes, you can design an SSIS package with Connection Manager properties configured dynamically using project parameters, so you can swap out the server name or set a parameter called ENV to "DEV", "QA" or "PROD". That is useful when you have a package that connects to other remote servers. However, if the package only references local databases, you can just set ServerName to "(local)", and it will run in any environment without modification. If you have a stored procedure that references remote tables using linked server connections, then that's a good candidate for migrating to SSIS.
Thanks Eric for those suggestions/pointers:
* (local) if referencing local env, great point! I see that existing packages have been pre-2012 (ssis) and the connection manager properties have explicit host names even if 'local'
* If processes are referencing Linked server to place in SSIS thanks!
Good suggestion.. currently we have lots of dev who actually embed calling "linked servers" inside SSIS!
in addition Initiate linked server sproc calls?
They seem to be using a SQL component in 't-sql coe' to 'union' data from 2 difference sources inside
1. local connection
2. linkedserver data
to final output as an example... what is occurring in our org/coprobably is not 'good' practice based on what I'm hearing.. as we should take advantage of the connections. I should probably go read an SSIS book .. and SSIS celebrities (MVPs :P) then start to give more guidelines to the teams?
Any one have suggestions for good resources?
Thank you for all the responses!
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
February 15, 2017 at 1:15 pm
I agree with Eric M Russell's comment about linked servers. We used to use linked servers all over the place and they worked. Not well, but they worked.
We are in the process of migrating all non-realtime required data (mostly used for reports) into SSIS packages that will run hourly, weekly or nightly to handle the data pull into our data warehouse and anything that need near realtime will be done using service broker.
For our SSIS, we use the SQL 2012 SSIS catalog and set up an environment for LIVE on the live SSIS catalog server and for TEST and Dev on the test server. This way, if a developer wants to run their package against dev, they use the dev environment. Same thing with test. Environments are quite useful too if you change server names for any reason, you update it in 1 place and all of our SQL jobs out of that server will continue to run like nothing happened instead of having to manually edit each job on the server.
But there is the other side of it too - if it ain't broke, don't fix it. A lot of companies don't like it when developers use time to fix a problem that doesn't exist.
I'd build up a small buisness plan and explain what you want to do and bring it up to your boss. When I wanted to change some procedures where I work, I brought up a few little things that were performance/compatibility issues and asked to write a SQL coding standard document for my team. Boss supported it and thought it was a good idea so we got a new SQL coding standard which is slightly biased to some of my personal preferences (like having numbered release scripts even if order doesn't matter), but also makes things more consistent.
As for resources, MSDN is a good place:
https://msdn.microsoft.com/en-us/library/ms141026.aspx
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.
February 27, 2017 at 1:05 pm
I agree with Eric M Russell's comment about linked servers. We used to use linked servers all over the place and they worked. Not well, but they worked.
We are in the process of migrating all non-realtime required data (mostly used for reports) into SSIS packages that will run hourly, weekly or nightly to handle the data pull into our data warehouse and anything that need near realtime will be done using service broker.
For our SSIS, we use the SQL 2012 SSIS catalog and set up an environment for LIVE on the live SSIS catalog server and for TEST and Dev on the test server. This way, if a developer wants to run their package against dev, they use the dev environment. Same thing with test. Environments are quite useful too if you change server names for any reason, you update it in 1 place and all of our SQL jobs out of that server will continue to run like nothing happened instead of having to manually edit each job on the server.
But there is the other side of it too - if it ain't broke, don't fix it. A lot of companies don't like it when developers use time to fix a problem that doesn't exist.
I'd build up a small buisness plan and explain what you want to do and bring it up to your boss. When I wanted to change some procedures where I work, I brought up a few little things that were performance/compatibility issues and asked to write a SQL coding standard document for my team. Boss supported it and thought it was a good idea so we got a new SQL coding standard which is slightly biased to some of my personal preferences (like having numbered release scripts even if order doesn't matter), but also makes things more consistent.
As for resources, MSDN is a good place:
https://msdn.microsoft.com/en-us/library/ms141026.aspx %5B/quote%5D
Thank you for the reply, appreciate the feedback - this is a great start for me. I'll have to work on refining some documentation for the future.
Thank you
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply